--<V>--
----------------------------------------------------------------------------------------------------------------
-- SCRIPT DESCRIPTION:	Script to build FACT_CAN_NULLS_PLAN_A
----------------------------------------------------------------------------------------------------------------
-- ENVIRONMENT:			DEVELOPLEMENT (Implemented on 05/11/2007)
-- LOCATION:			C:\workspace\Development_Files\Application_Fact
-- NEXT:				After successful run for 7 days, script to be moved to PRODUCTION (on 12/11/2007)
----------------------------------------------------------------------------------------------------------------
-- VERSION CONTROL
----------------------------------------------------------------------------------------------------------------
--		Version		Date		Emp		Comments
--		----------	----------	------	---------------------------------
--		V1			01/11/2007	Vicus	Script Created
--		V2			05/11/2007	Emile	Passed
--		V3			05/11/2007	Emile	Script Prepared for Batch Run (Batch_Fact_CAN_NULLS.bat)
--										Batch run set up to run 4:00 every morning
--										RCLPortal log script added for CAN_FACT_NULLS
--		V4			20/11/2007	Emile	Script migrated to accomodate for the new CAN PLAN A database
--										(CanNcaTransactional) - still need to run old script in
--										conjuction with this one.

--		V5			19/06/2008	Lentswe	Script passed
--					19/06/2008	Lentswe	Script Changed to create CAN_Fact and to eliminate possible duplicates 
--								Lentswe Script to sort out performance issues by using indexes
--		V6			18/11/2009	Magda Rewrite for Data Governance

----------------------------------------------------------------------------------------------------------------
--</V>--
--<O>--
--Lentswe Phoshoko
--</O>--
--<U>--
use STAGINGDB
--</U>--
----STRT_CMT1_(CREATE CAN_FACT)----END_CMT1
----STRT_CMT2_(DATABASE TO BE USED can_nca_b)----END_CMT2
----STRT_CMT3_(DECLARE GLOBAL VARIABLES)----END_CMT3
--<M>--
declare @star as int
declare @dist as int
--</M>--

----STRT_CMT4_(THE MAXIMUM applicationid IS USED TO DETERMINE THE MOST RECENT APPLICATION)----END_CMT4
----STRT_CMT5_(REMOVE DUPLICATES FROM THE m_application TABLE)----END_CMT5

----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.ApplicationState') is not null 
begin drop table dbo.ApplicationState end
--</M>--
go
Select
--<S>--
	a.originalapplicationid as originalapplicationid, 
	a.srv as srv, 
	max(b.applicationid) as applicationid
--</S>--
into 
--<I>--
dbo.ApplicationState
--</I>--

from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_ApplicationState b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
        1=2

group by a.originalapplicationid, 
		 a.srv
--</F>--
--</SBLK>--

INSERT INTO 
--<II>--
dbo.ApplicationState
--</II>--
Select
--<S>--
	a.originalapplicationid as originalapplicationid, 
	a.srv as srv, 
	max(b.applicationid) as applicationid
--</S>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_ApplicationState b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0        

group by a.originalapplicationid, 
		 a.srv
--</F>--
CREATE CLUSTERED INDEX
--<X>--
[IX_PK_applicationid] ON [dbo].[ApplicationState] 
([applicationid] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
---------------------------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_AppStatus') is not null 
begin drop table dbo.fc_Master_Lookup_AppStatus end
--</M>--
select	
--<S>--
	a.originalapplicationid as originalapplicationid, 
	b.ApplicationDecisionId as ApplicationDecisionId,
	b.ApplicationId as ApplicationId,
	b.ApplicationStateId as ApplicationStateId,
	b.AssessmentComments as AssessmentComments,
	b.DocumentsOustanding as DocumentsOustanding,
	b.IsReCalcFailed as IsReCalcFailed,
	b.PreAppealStateId as PreAppealStateId,
	b.StaffwareCase as StaffwareCase,
	b.StaffwareStateId as StaffwareStateId,
	b.StaffwareSubmitDate as StaffwareSubmitDate,
	b.TransactStateId as TransactStateId,
	b.TransactSubmitDate as TransactSubmitDate,
	b.srv as srv 
--</S>--
into	
--<I>--
	dbo.fc_Master_Lookup_AppStatus 
--</I>--
from	
--<F>--
		ApplicationState a 
		left outer join 
		dbo.Union_m_ApplicationState b 

on		a.applicationid = b.applicationid 
		and  
		a.srv = b.srv

Where	b.applicationid is not null and 
		1=2
--</F>--
--</SBLK>--


INSERT INTO 
--<II>--
	dbo.fc_Master_Lookup_AppStatus 
--</II>--
select	
--<S>--
	a.originalapplicationid as originalapplicationid, 
	b.ApplicationDecisionId as ApplicationDecisionId,
	b.ApplicationId as ApplicationId,
	b.ApplicationStateId as ApplicationStateId,
	b.AssessmentComments as AssessmentComments,
	b.DocumentsOustanding as DocumentsOustanding,
	b.IsReCalcFailed as IsReCalcFailed,
	b.PreAppealStateId as PreAppealStateId,
	b.StaffwareCase as StaffwareCase,
	b.StaffwareStateId as StaffwareStateId,
	b.StaffwareSubmitDate as StaffwareSubmitDate,
	b.TransactStateId as TransactStateId,
	b.TransactSubmitDate as TransactSubmitDate,
	b.srv as srv 
--</S>--
from	
--<F>--
		ApplicationState a 
		left outer join 
		dbo.Union_m_ApplicationState b 

on		a.applicationid = b.applicationid 
		and  
		a.srv = b.srv

Where	b.applicationid is not null 

--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON [dbo].[fc_Master_Lookup_AppStatus] 
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Initiation_Source') is not null 
begin drop table dbo.Initiation_Source end
--</M>--
select
--<S>--	
		a.originalapplicationid as originalapplicationid , 
		a.srv as srv, 
		max(b.applicationid) as applicationid
--</S>--	
into	
--<I>--
		dbo.Initiation_Source
--</I>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_Initiation b 


on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2

group by a.originalapplicationid, 
		 a.srv
--</F>--
--</SBLK>--

INSERT INTO 
--<II>--
	dbo.Initiation_Source
--</II>--
select
--<S>--	
		a.originalapplicationid as originalapplicationid , 
		a.srv as srv, 
		max(b.applicationid) as applicationid
--</S>--	
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_Initiation b 


on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 
		
group by a.originalapplicationid, 
		 a.srv
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON [dbo].[Initiation_Source] 
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_Initiation_Source') is not null 
begin drop table dbo.fc_Master_Lookup_Initiation_Source end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.ApplicationId as ApplicationId,
		b.ApplicationSource as ApplicationSource,
		b.BranchCode as BranchCode,
		b.BrandCode as BrandCode,
		b.Dcar as Dcar,
		b.DcarPrefix as DcarPrefix,
		b.Email as Email,
		b.Fax as Fax,
		b.FullName as FullName,
		b.InitiationSource as InitiationSource,
		b.IntermediaryName as IntermediaryName,
		b.OriginationSourceId as OriginationSourceId,
		b.OriginatorName as OriginatorName ,
		b.PercentageCommission as PercentageCommission,
		b.Phone as Phone,
		b.TaxiAssociationId as TaxiAssociationId,
		b.[User] as [User],
		b.srv as srv,
		b.CommissionIndicatorId as CommissionIndicatorId,
		b.PanelName as PanelName 

--</S>--
into
--<I>--
	dbo.fc_Master_Lookup_Initiation_Source 
--</I>--

from
--<F>--
		dbo.Initiation_Source a 
		left outer join 
		dbo.Union_m_Initiation b 


on		a.applicationid = b.applicationid
		and 
		a.srv = b.srv

where	b.applicationid is not null and 
		1=2
--</F>--
--</SBLK>--

INSERT INTO 
--<II>--
	dbo.fc_Master_Lookup_Initiation_Source 
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid , 
		b.ApplicationId as ApplicationId,
		b.ApplicationSource as ApplicationSource,
		b.BranchCode as BranchCode,
		b.BrandCode as BrandCode,
		b.Dcar as Dcar,
		b.DcarPrefix as DcarPrefix,
		b.Email as Email,
		b.Fax as Fax,
		b.FullName as FullName,
		b.InitiationSource as InitiationSource,
		b.IntermediaryName as IntermediaryName,
		b.OriginationSourceId as OriginationSourceId,
		b.OriginatorName as OriginatorName ,
		b.PercentageCommission as PercentageCommission,
		b.Phone as Phone,
		b.TaxiAssociationId as TaxiAssociationId,
		b.[User] as [User],
		b.srv as srv,
		b.CommissionIndicatorId as CommissionIndicatorId,
		b.PanelName as PanelName 

--</S>--
from
--<F>--
		dbo.Initiation_Source a 
		left outer join 
		dbo.Union_m_Initiation b 


on		a.applicationid = b.applicationid
		and 
		a.srv = b.srv

where	b.applicationid is not null  
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_Initiation_Source  
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------

--<BLK>--
--<SBLK>--
----STRT_CMT6_(OPS CHANGE START)----END_CMT6
--<M>--
if object_id('dbo.TakenUp_Max') is not null 
begin drop table dbo.TakenUp_Max end
--</M>--
select 
--<S>--
	   originalapplicationid as originalapplicationid, 
	   max(applicationid) as applicationid, 
	   srv as srv 
--</S>--
into	
--<I>--
	dbo.TakenUp_Max
--</I>--
from	
--<F>--
	dbo.union_m_application 

where	takenup = 1 and 
		productid in (4,5)and
		applicationid is not null and
		1=2

group by originalapplicationid, 
		 srv 
--</F>--
--</SBLK>--

INSERT INTO 
--<II>--
		dbo.TakenUp_Max
--</II>--
select 
--<S>--
	   originalapplicationid as originalapplicationid, 
	   max(applicationid) as applicationid, 
	   srv as srv 
--</S>--
from	
--<F>--
	dbo.union_m_application 

where	takenup = 1 and 
		productid in (4,5)and
		applicationid is not null 
group by
		 originalapplicationid, 
		 srv 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.TakenUp_Max 
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
---------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.TakenUpTOB') is not null 
begin drop table dbo.TakenUpTOB end
--</M>--
select  
--<S>--
		a.originalapplicationid as originalapplicationid  , 
		a.srv as srv, 
		b.Rate as TakenUpTOBRate, 
		b.DecisionCode as TakenUpTOBDecision, 
		b.Term as TakenUpTOBTerm, 
		b.NVFNetLoan as TakenUpTOBNVFNetLoan, 
		b.CaTotalLimit as TakenUpTOBCaTotalLimit,
		b.ScoreNGR as TakenUpTOBScoreNGR, 
		b.ScoreScaled as TakenUpTOBScoreScaled, 
		b.BureauScore as TakenUpTOBBureauScor
--</S>--
into	
--<I>--
	dbo.TakenUpTOB
--</I>--
from	
--<F>--
		dbo.TakenUp_Max  a 
		inner join 
		dbo.Union_m_TermsOfBusiness b 

on		a.applicationid = b.applicationid and	
		a.srv = b.srv

Where   b.applicationid is not null and
		1=2
--</F>--
--</SBLK>--

INSERT INTO 
--<II>--
		dbo.TakenUpTOB
--</II>--
select  
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		b.Rate as TakenUpTOBRate, 
		b.DecisionCode as TakenUpTOBDecision, 
		b.Term as TakenUpTOBTerm, 
		b.NVFNetLoan as TakenUpTOBNVFNetLoan, 
		b.CaTotalLimit as TakenUpTOBCaTotalLimit,
		b.ScoreNGR as TakenUpTOBScoreNGR, 
		b.ScoreScaled as TakenUpTOBScoreScaled, 
		b.BureauScore as TakenUpTOBBureauScor
--</S>--
from	
--<F>--
		dbo.TakenUp_Max  a 
		inner join 
		dbo.Union_m_TermsOfBusiness b 

on		a.applicationid = b.applicationid and	
		a.srv = b.srv

Where   b.applicationid is not null
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.TakenUpTOB 
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.TermsOfBusiness') is not null 
begin drop table dbo.TermsOfBusiness end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid, 
		min(b.applicationid) as First_applicationid
--</S>--
into
--<I>--
		dbo.TermsOfBusiness 
--</I>--

from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_TermsOfBusiness b 

on		a.applicationid = b.applicationid and 
		a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and 
		1=2

group by a.originalapplicationid, 
		 a.srv

--</F>--
--</SBLK>--

INSERT INTO 
--<II>--
		dbo.TermsOfBusiness 
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid, 
		min(b.applicationid) as First_applicationid
--</S>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_TermsOfBusiness b 

on		a.applicationid = b.applicationid and 
		a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0
		
group by a.originalapplicationid, 
		 a.srv
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.TermsOfBusiness 
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------from here-------------------------


--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Initial_TOB') is not null 
begin drop table dbo.Initial_TOB end
--</M>--
select 
--<S>--
	   a.originalapplicationid as originalapplicationid, 
	   a.srv as srv, 
	   b.Rate as InitialTOBRate, 
	   b.DecisionCode as InitialTOBDecision, 
	   b.Term as InitialTOBTerm, 
	   b.NVFNetLoan as InitialTOBNVFNetLoan, 
	   b.CaTotalLimit as InitialTOBCaTotalLimit,
	   b.ScoreNGR as InitialTOBScoreNGR, 
	   b.ScoreScaled as InitialTOBScoreScaled, 
	   b.BureauScore as InitialTOBBureauScore 
--</S>--
into   
--<I>--
		dbo.Initial_TOB
--</I>--

from
--<F>--
	   dbo.TermsOfBusiness a
	   left outer join 
	   Union_m_TermsOfBusiness b 

on	   a.first_applicationid = b.applicationid and 
	   a.srv = b.srv

Where 1=2
--</F>--
--</SBLK>--

INSERT INTO 
--<II>--
		dbo.Initial_TOB 
--</II>--
select 
--<S>--
	   a.originalapplicationid as originalapplicationid, 
	   a.srv as srv, 
	   b.Rate as InitialTOBRate, 
	   b.DecisionCode as InitialTOBDecision, 
	   b.Term as InitialTOBTerm, 
	   b.NVFNetLoan as InitialTOBNVFNetLoan, 
	   b.CaTotalLimit as InitialTOBCaTotalLimit,
	   b.ScoreNGR as InitialTOBScoreNGR, 
	   b.ScoreScaled as InitialTOBScoreScaled, 
	   b.BureauScore as InitialTOBBureauScore 
--</S>--
from
--<F>--
	   dbo.TermsOfBusiness a
	   left outer join 
	   Union_m_TermsOfBusiness b 

on	   a.first_applicationid = b.applicationid and 
	   a.srv = b.srv
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Initial_TOB 
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Final_TOB') is not null 
begin drop table dbo.Final_TOB end
--</M>--
select 
--<S>--
	   a.originalapplicationid as originalapplicationid, 
	   a.srv as srv, 
	   b.Rate as FinalTOBRate, 
       b.DecisionCode as FinalTOBDecision, 
	   b.Term as FinalTOBTerm, 
       b.NVFNetLoan as FinalTOBNVFNetLoan, 
       b.CaTotalLimit as FinalTOBCaTotalLimit,
	   b.ScoreNGR as FinalTOBScoreNGR, 
       b.ScoreScaled as FinalTOBScoreScaled, 
       b.BureauScore as FinalTOBBureauScore 
--</S>--
into   
--<I>--
	dbo.Final_TOB
--</I>--
from   
--<F>--
	   dbo.TermsOfBusiness a
	   left outer join 
       dbo.Union_m_TermsOfBusiness b 

on	   a.applicationid = b.applicationid and 
       a.srv = b.srv

Where 1=2
--</F>--
--</SBLK>--

INSERT INTO 
--<II>--
	dbo.Final_TOB
--</II>--
select 
--<S>--
	   a.originalapplicationid as originalapplicationid, 
	   a.srv as srv, 
	   b.Rate as FinalTOBRate, 
       b.DecisionCode as FinalTOBDecision, 
	   b.Term as FinalTOBTerm, 
       b.NVFNetLoan as FinalTOBNVFNetLoan, 
       b.CaTotalLimit as FinalTOBCaTotalLimit,
	   b.ScoreNGR as FinalTOBScoreNGR, 
       b.ScoreScaled as FinalTOBScoreScaled, 
       b.BureauScore as FinalTOBBureauScore 
--</S>--
from   
--<F>--
	   dbo.TermsOfBusiness a
	   left outer join 
       dbo.Union_m_TermsOfBusiness b 

on	   a.applicationid = b.applicationid and 
	   a.srv = b.srv
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Final_TOB 
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Final_TakenUp_TOB') is not null 
begin drop table dbo.Final_TakenUp_TOB end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid ,
		a.srv as srv,
		a.InitialTOBRate as InitialTOBRate,
		a.InitialTOBDecision as InitialTOBDecision,
		a.InitialTOBTerm as InitialTOBTerm,
		a.InitialTOBNVFNetLoan as InitialTOBNVFNetLoan,
		a.InitialTOBCaTotalLimit as InitialTOBCaTotalLimit,
		a.InitialTOBScoreNGR as InitialTOBScoreNGR,
		a.InitialTOBScoreScaled as InitialTOBScoreScaled ,
		a.InitialTOBBureauScore as InitialTOBBureauScore,
		b.FinalTOBRate as FinalTOBRate, 
		b.FinalTOBDecision as FinalTOBDecision, 
		b.FinalTOBTerm as FinalTOBTerm  , 
		b.FinalTOBNVFNetLoan as FinalTOBNVFNetLoan, 
		b.FinalTOBCaTotalLimit as FinalTOBCaTotalLimit,
		b.FinalTOBScoreNGR as FinalTOBScoreNGR , 
		b.FinalTOBScoreScaled as FinalTOBScoreScaled, 
		b.FinalTOBBureauScore as FinalTOBBureauScore,
		c.TakenUpTOBRate as TakenUpTOBRate , 
		c.TakenUpTOBDecision as TakenUpTOBDecision, 
		c.TakenUpTOBTerm as TakenUpTOBTerm, 
		c.TakenUpTOBNVFNetLoan as TakenUpTOBNVFNetLoan, 
		c.TakenUpTOBCaTotalLimit as TakenUpTOBCaTotalLimit,
		c.TakenUpTOBScoreNGR as TakenUpTOBScoreNGR, 
		c.TakenUpTOBScoreScaled as TakenUpTOBScoreScaled
--</S>--
into	
--<I>--
	dbo.Final_TakenUp_TOB
--</I>--
from	
--<F>--
		dbo.Initial_TOB a
		left outer join 
		dbo.Final_TOB b

on		a.originalapplicationid = b.originalapplicationid and 
		a.srv = b.srv
		left outer join 
		dbo.takenuptob c 
on		a.originalapplicationid = c.originalapplicationid and	
		a.srv = c.srv

Where	1=2
--</F>--
--</SBLK>--

INSERT INTO 
--<II>--
	dbo.Final_TakenUp_TOB
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid ,
		a.srv as srv,
		a.InitialTOBRate as InitialTOBRate,
		a.InitialTOBDecision as InitialTOBDecision,
		a.InitialTOBTerm as InitialTOBTerm,
		a.InitialTOBNVFNetLoan as InitialTOBNVFNetLoan,
		a.InitialTOBCaTotalLimit as InitialTOBCaTotalLimit,
		a.InitialTOBScoreNGR as InitialTOBScoreNGR,
		a.InitialTOBScoreScaled as InitialTOBScoreScaled ,
		a.InitialTOBBureauScore as InitialTOBBureauScore,
		b.FinalTOBRate as FinalTOBRate, 
		b.FinalTOBDecision as FinalTOBDecision, 
		b.FinalTOBTerm as FinalTOBTerm  , 
		b.FinalTOBNVFNetLoan as FinalTOBNVFNetLoan, 
		b.FinalTOBCaTotalLimit as FinalTOBCaTotalLimit,
		b.FinalTOBScoreNGR as FinalTOBScoreNGR , 
		b.FinalTOBScoreScaled as FinalTOBScoreScaled, 
		b.FinalTOBBureauScore as FinalTOBBureauScore,
		c.TakenUpTOBRate as TakenUpTOBRate , 
		c.TakenUpTOBDecision as TakenUpTOBDecision, 
		c.TakenUpTOBTerm as TakenUpTOBTerm, 
		c.TakenUpTOBNVFNetLoan as TakenUpTOBNVFNetLoan, 
		c.TakenUpTOBCaTotalLimit as TakenUpTOBCaTotalLimit,
		c.TakenUpTOBScoreNGR as TakenUpTOBScoreNGR, 
		c.TakenUpTOBScoreScaled as TakenUpTOBScoreScaled
--</S>--
from	
--<F>--
		dbo.Initial_TOB a
		left outer join 
		dbo.Final_TOB b

on		a.originalapplicationid = b.originalapplicationid and 
		a.srv = b.srv
		left outer join 
		dbo.takenuptob c 
on		a.originalapplicationid = c.originalapplicationid and	
		a.srv = c.srv
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Final_TakenUp_TOB
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_TOB') is not null 
begin drop table dbo.fc_Master_Lookup_TOB end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid , 
		b.AddendumRateFactor as AddendumRateFactor,
		b.ApplicationId 	as	ApplicationId 		,
		b.BalloonDate 	as	BalloonDate 		,
		b.Bureau 	as	Bureau 		,
		b.BureauAdverses 	as	BureauAdverses 		,
		b.BureauDebtCounseling 	as	BureauDebtCounseling 		,
		b.BureauDispute 	as	BureauDispute 		,
		b.BureauEnquiryDate 	as	BureauEnquiryDate 		,
		b.BureauIdVerify 	as	BureauIdVerify 		,
		b.BureauJudgements 	as	BureauJudgements 		,
		b.BureauNotices 	as	BureauNotices 		,
		b.BureauScore 	as	BureauScore 		,
		b.CAAdditionalLimit 	as	CAAdditionalLimit 		,
		b.CATotalLimit 	as	CATotalLimit 		,
		b.CG1Sector 	as	CG1Sector 		,
		b.DecisionCode 	as	DecisionCode 		,
		b.Deposit 	as	Deposit 		,
		b.ExpiryDate 	as	ExpiryDate 		,
		b.FixedRatePeriod 	as	FixedRatePeriod 		,
		b.FLTotal 	as	FLTotal 		,
		b.FraudReferral 	as	FraudReferral 		,
		b.HLCalculated 	as	HLCalculated 		,
		b.HLLoan 	as	HLLoan 		,
		b.HLLoanToValue 	as	HLLoanToValue 		,
		b.HLValuation 	as	HLValuation 		,
		b.InitiationFee 	as	InitiationFee 		,
		b.InstallIncl 	as	InstallIncl 		,
		b.Installment 	as	Installment 		,
		b.Interest 	as	Interest 		,
		b.IsAppealAllowed 	as	IsAppealAllowed 		,
		b.LastPaymentDate 	as	LastPaymentDate 		,
		b.LPAMandatory	as	LPAMandatory		,
		b.NVFBalloon 	as	NVFBalloon 		,
		b.NVFDocFeePerc 	as	NVFDocFeePerc 		,
		b.NVFDocumentationFee	as	NVFDocumentationFee		,
		b.NVFDocumentationFeePercentage	as	NVFDocumentationFeePercentage		,
		b.NVFNetLoan 	as	NVFNetLoan 		,
		b.NVFPurchase 	as	NVFPurchase 		,
		b.Prime 	as	Prime 		,
		b.PrimeFactor	as	PrimeFactor		,
		b.RaisingFee 	as	RaisingFee 		,
		b.Rate 	as	Rate 		,
		b.RateThreshold 	as	RateThreshold 		,
		b.RateTypeId 	as	RateTypeId 		,
		b.RepaymentFrequencyId	as	RepaymentFrequencyId		,
		b.RevertRateTo 	as	RevertRateTo 		,
		b.RevertRateToFactor 	as	RevertRateToFactor 		,
		b.ScoreNGR 	as	ScoreNGR 		,
		b.ScoreScaled 	as	ScoreScaled 		,
		b.Term 	as	Term 		,
		b.srv	as	srv		,
		c.InitialTOBRate as InitialTOBRate, 
		c.InitialTOBDecision as InitialTOBDecision, 
		c.InitialTOBTerm as InitialTOBTerm , 
		c.InitialTOBNVFNetLoan as InitialTOBNVFNetLoan , 
		c.InitialTOBCaTotalLimit as InitialTOBCaTotalLimit ,
		c.InitialTOBScoreNGR as InitialTOBScoreNGR , 
		c.InitialTOBScoreScaled as InitialTOBScoreScaled , 
		c.InitialTOBBureauScore as InitialTOBBureauScore,
		c.FinalTOBRate as FinalTOBRate , 
		c.FinalTOBDecision as FinalTOBDecision , 
		c.FinalTOBTerm as FinalTOBTerm , 
		c.FinalTOBNVFNetLoan as FinalTOBNVFNetLoan , 
		c.FinalTOBCaTotalLimit as FinalTOBCaTotalLimit ,
		c.FinalTOBScoreNGR as FinalTOBScoreNGR , 
		c.FinalTOBScoreScaled as FinalTOBScoreScaled , 
		c.FinalTOBBureauScore as FinalTOBBureauScore  ,
		c.TakenUpTOBRate as TakenUpTOBRate , 
		c.TakenUpTOBDecision as TakenUpTOBDecision , 
		c.TakenUpTOBTerm as TakenUpTOBTerm, 
		c.TakenUpTOBNVFNetLoan as TakenUpTOBNVFNetLoan , 
		c.TakenUpTOBCaTotalLimit as TakenUpTOBCaTotalLimit,
		c.TakenUpTOBScoreNGR as TakenUpTOBScoreNGR , 
		c.TakenUpTOBScoreScaled as TakenUpTOBScoreScaled
--</S>--
into	
--<I>--
	dbo.fc_Master_Lookup_TOB
--</I>--
from	
--<F>--
		dbo.TermsOfBusiness a 
		left outer join 
		dbo.Union_m_TermsOfBusiness b 

on		a.applicationid = b.applicationid and 
		a.srv = b.srv
		left outer join 
		dbo.Final_TakenUp_TOB c
on		a.originalapplicationid = c.originalapplicationid and 
		a.srv = c.srv

where	1=2
--</F>--
--</SBLK>--

INSERT INTO 
--<II>--
	dbo.fc_Master_Lookup_TOB
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.AddendumRateFactor as AddendumRateFactor,
		b.ApplicationId 	as	ApplicationId 		,
		b.BalloonDate 	as	BalloonDate 		,
		b.Bureau 	as	Bureau 		,
		b.BureauAdverses 	as	BureauAdverses 		,
		b.BureauDebtCounseling 	as	BureauDebtCounseling 		,
		b.BureauDispute 	as	BureauDispute 		,
		b.BureauEnquiryDate 	as	BureauEnquiryDate 		,
		b.BureauIdVerify 	as	BureauIdVerify 		,
		b.BureauJudgements 	as	BureauJudgements 		,
		b.BureauNotices 	as	BureauNotices 		,
		b.BureauScore 	as	BureauScore 		,
		b.CAAdditionalLimit 	as	CAAdditionalLimit 		,
		b.CATotalLimit 	as	CATotalLimit 		,
		b.CG1Sector 	as	CG1Sector 		,
		b.DecisionCode 	as	DecisionCode 		,
		b.Deposit 	as	Deposit 		,
		b.ExpiryDate 	as	ExpiryDate 		,
		b.FixedRatePeriod 	as	FixedRatePeriod 		,
		b.FLTotal 	as	FLTotal 		,
		b.FraudReferral 	as	FraudReferral 		,
		b.HLCalculated 	as	HLCalculated 		,
		b.HLLoan 	as	HLLoan 		,
		b.HLLoanToValue 	as	HLLoanToValue 		,
		b.HLValuation 	as	HLValuation 		,
		b.InitiationFee 	as	InitiationFee 		,
		b.InstallIncl 	as	InstallIncl 		,
		b.Installment 	as	Installment 		,
		b.Interest 	as	Interest 		,
		b.IsAppealAllowed 	as	IsAppealAllowed 		,
		b.LastPaymentDate 	as	LastPaymentDate 		,
		b.LPAMandatory	as	LPAMandatory		,
		b.NVFBalloon 	as	NVFBalloon 		,
		b.NVFDocFeePerc 	as	NVFDocFeePerc 		,
		b.NVFDocumentationFee	as	NVFDocumentationFee		,
		b.NVFDocumentationFeePercentage	as	NVFDocumentationFeePercentage		,
		b.NVFNetLoan 	as	NVFNetLoan 		,
		b.NVFPurchase 	as	NVFPurchase 		,
		b.Prime 	as	Prime 		,
		b.PrimeFactor	as	PrimeFactor		,
		b.RaisingFee 	as	RaisingFee 		,
		b.Rate 	as	Rate 		,
		b.RateThreshold 	as	RateThreshold 		,
		b.RateTypeId 	as	RateTypeId 		,
		b.RepaymentFrequencyId	as	RepaymentFrequencyId		,
		b.RevertRateTo 	as	RevertRateTo 		,
		b.RevertRateToFactor 	as	RevertRateToFactor 		,
		b.ScoreNGR 	as	ScoreNGR 		,
		b.ScoreScaled 	as	ScoreScaled 		,
		b.Term 	as	Term 		,
		b.srv	as	srv		,
		c.InitialTOBRate as InitialTOBRate, 
		c.InitialTOBDecision as InitialTOBDecision, 
		c.InitialTOBTerm as InitialTOBTerm , 
		c.InitialTOBNVFNetLoan as InitialTOBNVFNetLoan , 
		c.InitialTOBCaTotalLimit as InitialTOBCaTotalLimit ,
		c.InitialTOBScoreNGR as InitialTOBScoreNGR , 
		c.InitialTOBScoreScaled as InitialTOBScoreScaled , 
		c.InitialTOBBureauScore as InitialTOBBureauScore,
		c.FinalTOBRate as FinalTOBRate , 
		c.FinalTOBDecision as FinalTOBDecision , 
		c.FinalTOBTerm as FinalTOBTerm , 
		c.FinalTOBNVFNetLoan as FinalTOBNVFNetLoan , 
		c.FinalTOBCaTotalLimit as FinalTOBCaTotalLimit ,
		c.FinalTOBScoreNGR as FinalTOBScoreNGR , 
		c.FinalTOBScoreScaled as FinalTOBScoreScaled , 
		c.FinalTOBBureauScore as FinalTOBBureauScore  ,
		c.TakenUpTOBRate as TakenUpTOBRate , 
		c.TakenUpTOBDecision as TakenUpTOBDecision , 
		c.TakenUpTOBTerm as TakenUpTOBTerm, 
		c.TakenUpTOBNVFNetLoan as TakenUpTOBNVFNetLoan , 
		c.TakenUpTOBCaTotalLimit as TakenUpTOBCaTotalLimit,
		c.TakenUpTOBScoreNGR as TakenUpTOBScoreNGR , 
		c.TakenUpTOBScoreScaled as TakenUpTOBScoreScaled
--</S>--
from	
--<F>--
		dbo.TermsOfBusiness a 
		left outer join 
		dbo.Union_m_TermsOfBusiness b 

on		a.applicationid = b.applicationid and 
		a.srv = b.srv
		left outer join 
		dbo.Final_TakenUp_TOB c
on		a.originalapplicationid = c.originalapplicationid and 
		a.srv = c.srv
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_TOB
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------second----------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.NVFLoanDetails') is not null 
begin drop table dbo.NVFLoanDetails end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid
--</S>--

into	
--<I>--
	dbo.NVFLoanDetails
--</I>--

from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_NVFLoanDetails b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2

group by a.originalapplicationid, a.srv
--</F>--
--</SBLK>--

INSERT INTO 
--<II>--
		dbo.NVFLoanDetails
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid
--</S>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_NVFLoanDetails b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0
		

group by a.originalapplicationid, a.srv
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.NVFLoanDetails
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--

--<M>--
if object_id('dbo.fc_Master_Lookup_NVFLoanDetails') is not null 
begin drop table dbo.fc_Master_Lookup_NVFLoanDetails end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid , 
		b.AdminFee	as	AdminFee	,
		b.ApplicationId	as	ApplicationId	,
		b.ArrearsAdvanceId	as	ArrearsAdvanceId	,
		b.BalloonAmount	as	BalloonAmount	,
		b.Commision	as	Commision	,
		b.DepositAmount	as	DepositAmount	,
		b.DocumentationFeeId	as	DocumentationFeeId	,
		b.FinanceTypeId	as	FinanceTypeId	,
		b.FirstPaymentDate	as	FirstPaymentDate	,
		b.IsSchemeClient	as	IsSchemeClient	,
		b.IsVATBack	as	IsVATBack	,
		b.LifeAccept	as	LifeAccept	,
		b.LoanAmount	as	LoanAmount	,
		b.NeedsNedcredit	as	NeedsNedcredit	,
		b.NetLoanAmount	as	NetLoanAmount	,
		b.PaymentFrequencyId	as	PaymentFrequencyId	,
		b.Premium	as	Premium	,
		b.PurchasePrice	as	PurchasePrice	,
		b.RateTypeId	as	RateTypeId	,
		b.RepaymentPeriod	as	RepaymentPeriod	,
		b.RequestedInstallment	as	RequestedInstallment	,
		b.SchemeCode	as	SchemeCode	,
		b.SignupBranch	as	SignupBranch	,
		b.StructureTypeId	as	StructureTypeId	,
		b.srv	as	srv	,
		b.PercentageDeposit	as	PercentageDeposit	,
		b.PercentageBalloon	as	PercentageBalloon	,
		b.DealerReqDocFee	as	DealerReqDocFee	,
		b.MonthToSkip	as	MonthToSkip	,
		b.PurchasePriceExcl	as	PurchasePriceExcl	
--</S>--
into	
--<I>--
dbo.fc_Master_Lookup_NVFLoanDetails
--</I>--

from	
--<F>--
		dbo.NVFLoanDetails a 
		left outer join 
		dbo.Union_m_NVFLoanDetails b 

on		a.applicationid = b.applicationid and 
		a.srv = b.srv 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and 
		1=2
--</F>--
--</SBLK>--

Insert into
--<II>--
		dbo.fc_Master_Lookup_NVFLoanDetails
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.AdminFee	as	AdminFee	,
		b.ApplicationId	as	ApplicationId	,
		b.ArrearsAdvanceId	as	ArrearsAdvanceId	,
		b.BalloonAmount	as	BalloonAmount	,
		b.Commision	as	Commision	,
		b.DepositAmount	as	DepositAmount	,
		b.DocumentationFeeId	as	DocumentationFeeId	,
		b.FinanceTypeId	as	FinanceTypeId	,
		b.FirstPaymentDate	as	FirstPaymentDate	,
		b.IsSchemeClient	as	IsSchemeClient	,
		b.IsVATBack	as	IsVATBack	,
		b.LifeAccept	as	LifeAccept	,
		b.LoanAmount	as	LoanAmount	,
		b.NeedsNedcredit	as	NeedsNedcredit	,
		b.NetLoanAmount	as	NetLoanAmount	,
		b.PaymentFrequencyId	as	PaymentFrequencyId	,
		b.Premium	as	Premium	,
		b.PurchasePrice	as	PurchasePrice	,
		b.RateTypeId	as	RateTypeId	,
		b.RepaymentPeriod	as	RepaymentPeriod	,
		b.RequestedInstallment	as	RequestedInstallment	,
		b.SchemeCode	as	SchemeCode	,
		b.SignupBranch	as	SignupBranch	,
		b.StructureTypeId	as	StructureTypeId	,
		b.srv	as	srv	,
		b.PercentageDeposit	as	PercentageDeposit	,
		b.PercentageBalloon	as	PercentageBalloon	,
		b.DealerReqDocFee	as	DealerReqDocFee	,
		b.MonthToSkip	as	MonthToSkip	,
		b.PurchasePriceExcl	as	PurchasePriceExcl	
--</S>--
from	
--<F>--
		dbo.NVFLoanDetails a 
		left outer join 
		dbo.Union_m_NVFLoanDetails b 

on		a.applicationid = b.applicationid and 
		a.srv = b.srv 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 
--</F>--

CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_NVFLoanDetails
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.NVFAsset') is not null 
begin drop table dbo.NVFAsset end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid, 
		max(assetid) as assetid
--</S>--

into	
--<I>--
		dbo.NVFAsset
--</I>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_NVFAsset b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and 
		1=2

group by a.originalapplicationid,
		 a.srv 
--</F>--
--</SBLK>--

Insert into
--<II>--
		dbo.NVFAsset
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid , 
		a.srv as srv, 
		max(b.applicationid) as applicationid, 
		max(assetid) as assetid
--</S>--

from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_NVFAsset b 

on		a.applicationid = b.applicationid 


where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0  
		
group by a.originalapplicationid,
		 a.srv 
--</F>--

CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.NVFAsset
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_lookup_NVFAsset') is not null 
begin drop table dbo.fc_Master_lookup_NVFAsset end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid , 
		b.ApplicationId	as	ApplicationId	,
		b.AssetId	as	AssetId	,
		b.Condition	as	Condition	,
		b.ExtrasDescription1	as	ExtrasDescription1	,
		b.ExtrasDescription2	as	ExtrasDescription2	,
		b.ExtrasDescription3	as	ExtrasDescription3	,
		b.ExtrasDescription4	as	ExtrasDescription4	,
		b.ExtrasDescription5	as	ExtrasDescription5	,
		b.ExtrasDescriptionOther	as	ExtrasDescriptionOther	,
		b.ExtrasValue1	as	ExtrasValue1	,
		b.ExtrasValue2	as	ExtrasValue2	,
		b.ExtrasValue3	as	ExtrasValue3	,
		b.ExtrasValue4	as	ExtrasValue4	,
		b.ExtrasValue5	as	ExtrasValue5	,
		b.ExtrasValueOther	as	ExtrasValueOther	,
		b.IsNew	as	IsNew	,
		b.KM	as	KM	,
		b.Make	as	Make	,
		b.MMCode	as	MMCode	,
		b.Model	as	Model	,
		b.NoExtras	as	NoExtras	,
		b.RetailValue	as	RetailValue	,
		b.RetailValue1	as	RetailValue1	,
		b.RetailValue2	as	RetailValue2	,
		b.RetailValue3	as	RetailValue3	,
		b.RetailValue4	as	RetailValue4	,
		b.SpecifyOther	as	SpecifyOther	,
		b.TradeValue	as	TradeValue	,
		b.TradeValue1	as	TradeValue1	,
		b.TradeValue2	as	TradeValue2	,
		b.TradeValue3	as	TradeValue3	,
		b.TradeValue4	as	TradeValue4	,
		b.[Year]	as	[Year]	,
		b.srv	as	srv	,
		b.ExpectedKM	as	ExpectedKM	
--</S>--
into	
--<I>--
		dbo.fc_Master_lookup_NVFAsset
--</I>--
from	
--<F>--
		dbo.NVFAsset a 
		left outer join 
		dbo.Union_m_NVFAsset b 

on		a.Applicationid = b.applicationid and 
		a.assetid = b.assetid and 
		a.srv = b.srv

Where	b.Applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2
--</F>--
--</SBLK>--

Insert Into
--<II>--
		dbo.fc_Master_lookup_NVFAsset
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.ApplicationId	as	ApplicationId	,
		b.AssetId	as	AssetId	,
		b.Condition	as	Condition	,
		b.ExtrasDescription1	as	ExtrasDescription1	,
		b.ExtrasDescription2	as	ExtrasDescription2	,
		b.ExtrasDescription3	as	ExtrasDescription3	,
		b.ExtrasDescription4	as	ExtrasDescription4	,
		b.ExtrasDescription5	as	ExtrasDescription5	,
		b.ExtrasDescriptionOther	as	ExtrasDescriptionOther	,
		b.ExtrasValue1	as	ExtrasValue1	,
		b.ExtrasValue2	as	ExtrasValue2	,
		b.ExtrasValue3	as	ExtrasValue3	,
		b.ExtrasValue4	as	ExtrasValue4	,
		b.ExtrasValue5	as	ExtrasValue5	,
		b.ExtrasValueOther	as	ExtrasValueOther	,
		b.IsNew	as	IsNew	,
		b.KM	as	KM	,
		b.Make	as	Make	,
		b.MMCode	as	MMCode	,
		b.Model	as	Model	,
		b.NoExtras	as	NoExtras	,
		b.RetailValue	as	RetailValue	,
		b.RetailValue1	as	RetailValue1	,
		b.RetailValue2	as	RetailValue2	,
		b.RetailValue3	as	RetailValue3	,
		b.RetailValue4	as	RetailValue4	,
		b.SpecifyOther	as	SpecifyOther	,
		b.TradeValue	as	TradeValue	,
		b.TradeValue1	as	TradeValue1	,
		b.TradeValue2	as	TradeValue2	,
		b.TradeValue3	as	TradeValue3	,
		b.TradeValue4	as	TradeValue4	,
		b.[Year]	as	[Year]	,
		b.srv	as	srv	,
		b.ExpectedKM	as	ExpectedKM	
--</S>--
from	
--<F>--
		dbo.NVFAsset a 
		left outer join 
		dbo.Union_m_NVFAsset b 

on		a.applicationid = b.applicationid and 
		a.assetid = b.assetid and 
		a.srv = b.srv

Where	b.Applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 
		

--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appassetsrv] ON dbo.fc_Master_lookup_NVFAsset
([applicationid],[srv],[assetid] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--

--<M>--
if object_id('dbo.AppStructure') is not null 
begin drop table dbo.AppStructure end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid 
--</S>--
into	
--<I>--
		dbo.AppStructure
--</I>--

from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_ApplicationStructure b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and 
		b.SecurityCount <> 0 and 
		1=2

group by a.originalapplicationid, 
		 a.srv 
--</F>--
--</SBLK>--

Insert into
--<II>--
		dbo.AppStructure
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid 
--</S>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_ApplicationStructure b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and 
		b.SecurityCount <> 0 
		

group by a.originalapplicationid, 
		 a.srv 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.AppStructure
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_AppStructure') is not null 
begin drop table dbo.fc_Master_Lookup_AppStructure end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.ApplicationId as ApplicationId,
		b.CoApplicantCount as CoApplicantCount,
		b.SecurityCount as SecurityCount,
		b.SuretyCount as SuretyCount,
		b.srv as srv
--</S>--
into	
--<I>--
	dbo.fc_Master_Lookup_AppStructure
--</I>--

from	
--<F>--
		dbo.AppStructure a 
		left outer join 
		dbo.Union_m_ApplicationStructure b

on		a.applicationid = b.applicationid and	
		a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2
--</F>--
--</SBLK>--

Insert into
--<II>--
	dbo.fc_Master_Lookup_AppStructure
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.ApplicationId as ApplicationId,
		b.CoApplicantCount as CoApplicantCount,
		b.SecurityCount as SecurityCount,
		b.SuretyCount as SuretyCount,
		b.srv as srv
--</S>--
from	
--<F>--
		dbo.AppStructure a 
		left outer join 
		dbo.Union_m_ApplicationStructure b

on		a.applicationid = b.applicationid and	
		a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_AppStructure
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.CA_LoanDetails') is not null 
begin drop table dbo.CA_LoanDetails end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid,
		max(b.AdditionalLimit) as AdditionalLimit
--</S>--
into	
--<I>--
		dbo.CA_LoanDetails
--</I>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_CALoanDetails b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2

group by a.originalapplicationid, a.srv 
--</F>--
--</SBLK>--

Insert Into
--<II>--
		dbo.CA_LoanDetails
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid,
		max(b.AdditionalLimit) as AdditionalLimit
--</S>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_CALoanDetails b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 

group by a.originalapplicationid, a.srv 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.CA_LoanDetails
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_CALoanDetails') is not null 
begin drop table dbo.fc_Master_Lookup_CALoanDetails end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.AdditionalLimit	as	AdditionalLimit	,
		b.AnnualReviewConsent	as	AnnualReviewConsent	,
		b.ApplicationId	as	ApplicationId	,
		b.BeneficiaryDetails	as	BeneficiaryDetails	,
		b.CurrentLimit	as	CurrentLimit	,
		b.ExistingExpiryDate	as	ExistingExpiryDate	,
		b.ExistingRepayment	as	ExistingRepayment	,
		b.ExistingRepaymentAmount	as	ExistingRepaymentAmount	,
		b.ExpiryDate	as	ExpiryDate	,
		b.GuaranteeAmount	as	GuaranteeAmount	,
		b.Motivation	as	Motivation	,
		b.MustFacilityReduce	as	MustFacilityReduce	,
		b.PurposeOfFacility	as	PurposeOfFacility	,
		b.ReduceTo	as	ReduceTo	,
		b.ReductionAmount	as	ReductionAmount	,
		b.ReductionStartDate	as	ReductionStartDate	,
		b.ReductionTypeId	as	ReductionTypeId	,
		b.RepaymentPeriod	as	RepaymentPeriod	,
		b.RequestDescription	as	RequestDescription	,
		b.RequiredRate	as	RequiredRate	,
		b.RequiredRepayment	as	RequiredRepayment	,
		b.SourceOfRepayment	as	SourceOfRepayment	,
		b.TotalLimit	as	TotalLimit	,
		b.srv	as	srv	
 --</S>--
into	
--<I>--
		dbo.fc_Master_Lookup_CALoanDetails
--</I>--
from	
--<F>--
		dbo.CA_LoanDetails a 
		left outer join 
		dbo.Union_m_CALoanDetails b 

on		a.applicationid = b.applicationid and
		a.srv = b.srv 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2
--</F>--
--</SBLK>--

Insert into
--<II>--
		dbo.fc_Master_Lookup_CALoanDetails
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.AdditionalLimit	as	AdditionalLimit	,
		b.AnnualReviewConsent	as	AnnualReviewConsent	,
		b.ApplicationId	as	ApplicationId	,
		b.BeneficiaryDetails	as	BeneficiaryDetails	,
		b.CurrentLimit	as	CurrentLimit	,
		b.ExistingExpiryDate	as	ExistingExpiryDate	,
		b.ExistingRepayment	as	ExistingRepayment	,
		b.ExistingRepaymentAmount	as	ExistingRepaymentAmount	,
		b.ExpiryDate	as	ExpiryDate	,
		b.GuaranteeAmount	as	GuaranteeAmount	,
		b.Motivation	as	Motivation	,
		b.MustFacilityReduce	as	MustFacilityReduce	,
		b.PurposeOfFacility	as	PurposeOfFacility	,
		b.ReduceTo	as	ReduceTo	,
		b.ReductionAmount	as	ReductionAmount	,
		b.ReductionStartDate	as	ReductionStartDate	,
		b.ReductionTypeId	as	ReductionTypeId	,
		b.RepaymentPeriod	as	RepaymentPeriod	,
		b.RequestDescription	as	RequestDescription	,
		b.RequiredRate	as	RequiredRate	,
		b.RequiredRepayment	as	RequiredRepayment	,
		b.SourceOfRepayment	as	SourceOfRepayment	,
		b.TotalLimit	as	TotalLimit	,
		b.srv	as	srv	
 --</S>--
from	
--<F>--
		dbo.CA_LoanDetails a 
		left outer join 
		dbo.Union_m_CALoanDetails b 

on		a.applicationid = b.applicationid and
		a.srv = b.srv 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 		
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_CALoanDetails
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.PaymentMedium') is not null 
begin drop table dbo.PaymentMedium end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid , 
		a.srv as srv, 
		max(b.applicationid) as applicationid
--</S>--
into	
--<I>--
		dbo.PaymentMedium
--</I>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_PaymentMedium b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2

group by a.originalapplicationid, 
		 a.srv 
--</F>--
--</SBLK>--

Insert into
--<II>--
		dbo.PaymentMedium
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid , 
		a.srv as srv, 
		max(b.applicationid) as applicationid
--</S>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_PaymentMedium b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 
		
group by a.originalapplicationid, 
		 a.srv 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.PaymentMedium
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_PaymentMedium') is not null 
begin drop table dbo.fc_Master_Lookup_PaymentMedium end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.AccountNumber as AccountNumber,
		b.AccountTypeId as AccountTypeId,
		b.ApplicationId as ApplicationId,
		b.Bank as Bank,
		b.BranchName as BranchName,
		b.ClearingCode as ClearingCode,
		b.IsCurrentAccount as IsCurrentAccount,
		b.IsNedbankCA as IsNedbankCA,
		b.OtherAccountNumber as OtherAccountNumber,
		b.srv as srv,
		b.PaymentDueDate as PaymentDueDate,
		b.StatementFrequencyId as StatementFrequencyId 
--</S>--
into	
--<I>--
	dbo.fc_Master_Lookup_PaymentMedium
--</I>--
from	
--<F>--
		dbo.PaymentMedium a 
		left outer join 
		dbo.Union_m_PaymentMedium b 

on		a.applicationid = b.applicationid and 
		a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2
--</F>--
--</SBLK>--

Insert Into
--<II>--
	dbo.fc_Master_Lookup_PaymentMedium
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.AccountNumber as AccountNumber,
		b.AccountTypeId as AccountTypeId,
		b.ApplicationId as ApplicationId,
		b.Bank as Bank,
		b.BranchName as BranchName,
		b.ClearingCode as ClearingCode,
		b.IsCurrentAccount as IsCurrentAccount,
		b.IsNedbankCA as IsNedbankCA,
		b.OtherAccountNumber as OtherAccountNumber,
		b.srv as srv,
		b.PaymentDueDate as PaymentDueDate,
		b.StatementFrequencyId as StatementFrequencyId 
--</S>--
from	
--<F>--
		dbo.PaymentMedium a 
		left outer join 
		dbo.Union_m_PaymentMedium b 

on		a.applicationid = b.applicationid and 
		a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 
		
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_PaymentMedium
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------third----------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.RateChange') is not null 
begin drop table dbo.RateChange end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid , 
		a.srv as srv, 
		max(b.applicationid) as applicationid, 
		max(b.approvedInterestRate) as approvedInterestRate
--</S>--

into
--<I>--	
	dbo.RateChange
--</I>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_RateChange b
 
on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2

group by a.originalapplicationid, 
		 a.srv 
--</F>--
--</SBLK>--

Insert Into
--<II>--
	dbo.RateChange
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid , 
		a.srv as srv, 
		max(b.applicationid) as applicationid, 
		max(b.approvedInterestRate) as approvedInterestRate
--</S>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_RateChange b
 
on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 

group by a.originalapplicationid, 
		 a.srv 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.RateChange
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--

----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_RateChange') is not null 
begin drop table dbo.fc_Master_Lookup_RateChange end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.ApplicationId as ApplicationId,
		b.approvedInterestRate as approvedInterestRate ,
		b.Comments as Comments,
		b.DateCaptured as DateCaptured,
		b.Decision as Decision,
		b.newInterestRate as newInterestRate,
		b.RateAdjustmentId as RateAdjustmentId,
		b.Updated as Updated,
		b.[User] as [User],
		b.srv as srv

--</S>--
into	
--<I>--
	dbo.fc_Master_Lookup_RateChange
--</I>--

from	
--<F>--
		dbo.RateChange a 
		left outer join 
		dbo.Union_m_RateChange b

on		a.applicationid = b.applicationid and	
		a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2
--</F>--
--</SBLK>--

Insert Into
--<II>--
	dbo.fc_Master_Lookup_RateChange
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.ApplicationId as ApplicationId,
		b.approvedInterestRate as approvedInterestRate ,
		b.Comments as Comments,
		b.DateCaptured as DateCaptured,
		b.Decision as Decision,
		b.newInterestRate as newInterestRate,
		b.RateAdjustmentId as RateAdjustmentId,
		b.Updated as Updated,
		b.[User] as [User],
		b.srv as srv
--</S>--
from	
--<F>--
		dbo.RateChange a 
		left outer join 
		dbo.Union_m_RateChange b

on		a.applicationid = b.applicationid and	
		a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_RateChange
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.CashFlow') is not null 
begin drop table dbo.CashFlow end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid
--</S>--
into	
--<I>--
		dbo.CashFlow
--</I>--

from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_CashFlow b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2

group by a.originalapplicationid, 
		 a.srv
--</F>--
--</SBLK>--

Insert into
--<II>--
		dbo.CashFlow
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid
--</S>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_CashFlow b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 

group by a.originalapplicationid, 
		 a.srv
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.CashFlow
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_CashFlow') is not null 
begin drop table dbo.fc_Master_Lookup_CashFlow end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid , 
		b.ApplicationId  as ApplicationId,
		b.AverageCreditTurnover as AverageCreditTurnover,
		b.CashFlowMonth1  as CashFlowMonth1,
		b.CashFlowMonth10  as CashFlowMonth10 ,
		b.CashFlowMonth11  as CashFlowMonth11,
		b.CashFlowMonth12  as CashFlowMonth12,
		b.CashFlowMonth2  as CashFlowMonth2,
		b.CashFlowMonth3  as CashFlowMonth3,
		b.CashFlowMonth4 as CashFlowMonth4,
		b.CashFlowMonth5  as CashFlowMonth5,
		b.CashFlowMonth6  as CashFlowMonth6,
		b.CashFlowMonth7  as CashFlowMonth7,
		b.CashFlowMonth8  as CashFlowMonth8,
		b.CashFlowMonth9  as CashFlowMonth9,
		b.CashFlowTurnoverActual  as CashFlowTurnoverActual,
		b.DisposableCashActual  as DisposableCashActual,
		b.DisposableCashMonth1  as DisposableCashMonth1,
		b.DisposableCashMonth10  as DisposableCashMonth10,
		b.DisposableCashMonth11  as DisposableCashMonth11,
		b.DisposableCashMonth12  as DisposableCashMonth12,
		b.DisposableCashMonth2  as DisposableCashMonth2,
		b.DisposableCashMonth3  as DisposableCashMonth3,
		b.DisposableCashMonth4  as DisposableCashMonth4,
		b.DisposableCashMonth5  as DisposableCashMonth5,
		b.DisposableCashMonth6  as DisposableCashMonth6,
		b.DisposableCashMonth7  as DisposableCashMonth7,
		b.DisposableCashMonth8  as DisposableCashMonth8,
		b.DisposableCashMonth9  as DisposableCashMonth9,
		b.NegativeOpeningBankBalance  as NegativeOpeningBankBalance,
		b.NetProfitActual  as NetProfitActual,
		b.NetProfitMonth1  as NetProfitMonth1,
		b.NetProfitMonth10  as NetProfitMonth10,
		b.NetProfitMonth11  as NetProfitMonth11,
		b.NetProfitMonth12  as NetProfitMonth12,
		b.NetProfitMonth2  as NetProfitMonth2,
		b.NetProfitMonth3  as NetProfitMonth3,
		b.NetProfitMonth4  as NetProfitMonth4,
		b.NetProfitMonth5  as NetProfitMonth5,
		b.NetProfitMonth6  as NetProfitMonth6,
		b.NetProfitMonth7  as NetProfitMonth7,
		b.NetProfitMonth8  as NetProfitMonth8,
		b.NetProfitMonth9  as NetProfitMonth9,
		b.OpeningStock  as OpningStock,
		b.PositiveOpeningBankBalance as PositiveOpeningBankBalance,
		b.TotalCreditors  as TotalCreditors,
		b.TotalCurrentAssets  as TotalCurrentAssets,
		b.TotalCurrentLiabilities as TotalCurrentLiabilities,
		b.TotalDebtors  as TotalDebtors ,
		b.srv as srv
--</S>--
into	
--<I>--
		dbo.fc_Master_Lookup_CashFlow
--</I>--
from	
--<F>--
		dbo.CashFlow a 
		left outer join 
		dbo.Union_m_CashFlow b 

on		a.applicationid = b.applicationid and		
		a.srv = b.srv 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2
--</F>--
--</SBLK>--

Insert into
--<II>--
		dbo.fc_Master_Lookup_CashFlow
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid , 
		b.ApplicationId  as ApplicationId,
		b.AverageCreditTurnover as AverageCreditTurnover,
		b.CashFlowMonth1  as CashFlowMonth1,
		b.CashFlowMonth10  as CashFlowMonth10 ,
		b.CashFlowMonth11  as CashFlowMonth11,
		b.CashFlowMonth12  as CashFlowMonth12,
		b.CashFlowMonth2  as CashFlowMonth2,
		b.CashFlowMonth3  as CashFlowMonth3,
		b.CashFlowMonth4 as CashFlowMonth4,
		b.CashFlowMonth5  as CashFlowMonth5,
		b.CashFlowMonth6  as CashFlowMonth6,
		b.CashFlowMonth7  as CashFlowMonth7,
		b.CashFlowMonth8  as CashFlowMonth8,
		b.CashFlowMonth9  as CashFlowMonth9,
		b.CashFlowTurnoverActual  as CashFlowTurnoverActual,
		b.DisposableCashActual  as DisposableCashActual,
		b.DisposableCashMonth1  as DisposableCashMonth1,
		b.DisposableCashMonth10  as DisposableCashMonth10,
		b.DisposableCashMonth11  as DisposableCashMonth11,
		b.DisposableCashMonth12  as DisposableCashMonth12,
		b.DisposableCashMonth2  as DisposableCashMonth2,
		b.DisposableCashMonth3  as DisposableCashMonth3,
		b.DisposableCashMonth4  as DisposableCashMonth4,
		b.DisposableCashMonth5  as DisposableCashMonth5,
		b.DisposableCashMonth6  as DisposableCashMonth6,
		b.DisposableCashMonth7  as DisposableCashMonth7,
		b.DisposableCashMonth8  as DisposableCashMonth8,
		b.DisposableCashMonth9  as DisposableCashMonth9,
		b.NegativeOpeningBankBalance  as NegativeOpeningBankBalance,
		b.NetProfitActual  as NetProfitActual,
		b.NetProfitMonth1  as NetProfitMonth1,
		b.NetProfitMonth10  as NetProfitMonth10,
		b.NetProfitMonth11  as NetProfitMonth11,
		b.NetProfitMonth12  as NetProfitMonth12,
		b.NetProfitMonth2  as NetProfitMonth2,
		b.NetProfitMonth3  as NetProfitMonth3,
		b.NetProfitMonth4  as NetProfitMonth4,
		b.NetProfitMonth5  as NetProfitMonth5,
		b.NetProfitMonth6  as NetProfitMonth6,
		b.NetProfitMonth7  as NetProfitMonth7,
		b.NetProfitMonth8  as NetProfitMonth8,
		b.NetProfitMonth9  as NetProfitMonth9,
		b.OpeningStock  as OpningStock,
		b.PositiveOpeningBankBalance as PositiveOpeningBankBalance,
		b.TotalCreditors  as TotalCreditors,
		b.TotalCurrentAssets  as TotalCurrentAssets,
		b.TotalCurrentLiabilities as TotalCurrentLiabilities,
		b.TotalDebtors  as TotalDebtors ,
		b.srv as srv
--</S>--
from	
--<F>--
		dbo.CashFlow a 
		left outer join 
		dbo.Union_m_CashFlow b 

on		a.applicationid = b.applicationid and		
		a.srv = b.srv 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_CashFlow
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Freeze') is not null 
begin drop table dbo.Freeze end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv,
		max(b.applicationid) as applicationid, 
		max(freezeid) as freezeid
--</S>--
into	
--<I>--
		dbo.Freeze
--</I>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_Freeze b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2
group by a.originalapplicationid, a.srv 
--</F>--
--</SBLK>--

Insert into
--<II>--
		dbo.Freeze
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv,
		max(b.applicationid) as applicationid, 
		max(freezeid) as freezeid
--</S>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_Freeze b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 

group by a.originalapplicationid, a.srv 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Freeze
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_AppFreeze') is not null 
begin drop table dbo.fc_Master_Lookup_AppFreeze end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.ApplicationId as ApplicationId ,
		b.Comments as  Comments	,
		b.Date   as	 Date ,
		b.FreezeId 	as	FreezeId ,
		b.FreezeReason1 as	FreezeReason1,                             	
		b.FreezeReason10 as	 FreezeReason10,                              	
		b.FreezeReason11 as	FreezeReason11,                                 	
		b.FreezeReason12 as FreezeReason12,                                 	
		b.FreezeReason13 as	 FreezeReason13,                             	
		b.FreezeReason2 as FreezeReason2,                               			
		b.FreezeReason3 as FreezeReason3,                              	
		b.FreezeReason4 as FreezeReason4,                              
		b.FreezeReason5 as FreezeReason5,                            			
		b.FreezeReason6 as FreezeReason6,                           			
		b.FreezeReason7 as FreezeReason7,                           	
		b.FreezeReason8 as FreezeReason8,                                
		b.FreezeReason9  as	FreezeReason9,                               	
		b.Updated as Updated,                                 	
		b.[User]as [User],                                  	
		b.srv	as srv
--</S>-- 
into	
--<I>--
	dbo.fc_Master_Lookup_AppFreeze
--</I>--
from
--<F>--
		dbo.Freeze a 
		left outer join 
		dbo.Union_m_Freeze b 

on		a.applicationid = b.applicationid and 
		a.freezeid = b.freezeid and 
		a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2
--</F>--
--</SBLK>--

insert into
--<II>--
	dbo.fc_Master_Lookup_AppFreeze
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.ApplicationId as ApplicationId ,
		b.Comments as  Comments	,
		b.Date   as	 Date ,
		b.FreezeId 	as	FreezeId ,
		b.FreezeReason1 as	FreezeReason1,                             	
		b.FreezeReason10 as	 FreezeReason10,                              	
		b.FreezeReason11 as	FreezeReason11,                                 	
		b.FreezeReason12 as FreezeReason12,                                 	
		b.FreezeReason13 as	 FreezeReason13,                             	
		b.FreezeReason2 as FreezeReason2,                               			
		b.FreezeReason3 as FreezeReason3,                              	
		b.FreezeReason4 as FreezeReason4,                              
		b.FreezeReason5 as FreezeReason5,                            			
		b.FreezeReason6 as FreezeReason6,                           			
		b.FreezeReason7 as FreezeReason7,                           	
		b.FreezeReason8 as FreezeReason8,                                
		b.FreezeReason9  as	FreezeReason9,                               	
		b.Updated as Updated,                                 	
		b.[User]as [User],                                  	
		b.srv	as srv
--</S>-- 
from
--<F>--
		dbo.Freeze a 
		left outer join 
		dbo.Union_m_Freeze b 

on		a.applicationid = b.applicationid and 
		a.freezeid = b.freezeid and 
		a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_AppFreeze
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.AssetsLiabilities') is not null 
begin drop table dbo.AssetsLiabilities end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid
--</S>--
into	
--<I>--
	dbo.AssetsLiabilities
--</I>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_AssetsLiabilities b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2

group by a.originalapplicationid, 
		 a.srv 
--</F>--
--</SBLK>--

insert into
--<II>--
	dbo.AssetsLiabilities
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid
--</S>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_AssetsLiabilities b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 

group by a.originalapplicationid, 
		 a.srv 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.AssetsLiabilities
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------fourth----


--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_AssetsLiabilities') is not null 
begin drop table dbo.fc_Master_Lookup_AssetsLiabilities end
--</M>--
select
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.ApplicantRepositoryId as ApplicantRepositoryId,
		b.ApplicationId as ApplicationId,
		b.BankBalanceAsset as BankBalanceAsset,
		b.BankBalanceLiability as BankBalanceLiability,
		b.OpeningStock as OpeningStock,
		b.TotalCreditors as TotalCreditors,
		b.TotalCurrentAssets as TotalCurrentAssets,
		b.TotalCurrentLiabilities as TotalCurrentLiabilities,
		b.TotalDebtors as TotalDebtors,
		b.srv as srv 
--</S>--
into	
--<I>--
	dbo.fc_Master_Lookup_AssetsLiabilities
--</I>--
from	
--<F>--
		dbo.AssetsLiabilities a 
		left outer join 
		dbo.Union_m_AssetsLiabilities b 

on		a.applicationid = b.applicationid and 
		a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2
--</F>--
--</SBLK>--

insert into
--<II>--
	dbo.fc_Master_Lookup_AssetsLiabilities
--</II>--
select
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.ApplicantRepositoryId as ApplicantRepositoryId,
		b.ApplicationId as ApplicationId,
		b.BankBalanceAsset as BankBalanceAsset,
		b.BankBalanceLiability as BankBalanceLiability,
		b.OpeningStock as OpeningStock,
		b.TotalCreditors as TotalCreditors,
		b.TotalCurrentAssets as TotalCurrentAssets,
		b.TotalCurrentLiabilities as TotalCurrentLiabilities,
		b.TotalDebtors as TotalDebtors,
		b.srv as srv 
--</S>--

from	
--<F>--
		dbo.AssetsLiabilities a 
		left outer join 
		dbo.Union_m_AssetsLiabilities b 

on		a.applicationid = b.applicationid and 
		a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_AssetsLiabilities
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------fith--
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.TermLoan') is not null 
begin drop table dbo.TermLoan end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid
--</S>--
into	
--<I>--
	dbo.TermLoan
--</I>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_TermLoan b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and 
		1=2

group by a.originalapplicationid, 
		 a.srv 
--</F>--
--</SBLK>--

insert into
--<II>--
	dbo.TermLoan
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid
--</S>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_TermLoan b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 

group by a.originalapplicationid, 
		 a.srv 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.TermLoan
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------sixth

--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_TermLoan') is not null 
begin drop table dbo.fc_Master_Lookup_TermLoan end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.ApplicationId as ApplicationId,
		b.ArrearsAdvanceId as ArrearsAdvanceId,
		b.DocumentationFeeId as DocumentationFeeId,
		b.FinanceTypeId as FinanceTypeId,
		b.FirstPaymentDate as FirstPaymentDate,
		b.LoanAmount as LoanAmount,
		b.Motivation as Motivation,
		b.PaymentFrequencyId as PaymentFrequencyId,
		b.Rate as Rate,
		b.RateAuthorisation as RateAuthorisation,
		b.RateTypeId as RateTypeId,
		b.RateWithinPolicy as RateWithinPolicy,
		b.RepaymentPeriod as RepaymentPeriod,
		b.SourceOfRepayment as SourceOfRepayment,
		b.TermLoanPurposeId as TermLoanPurposeId,
		b.srv as srv
--</S>--
into	
--<I>--
	dbo.fc_Master_Lookup_TermLoan
--</I>--
from	
--<F>--
		dbo.TermLoan a 
		left outer join 
		dbo.Union_m_TermLoan b

on		a.applicationid = b.applicationid and 
		a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2
--</F>--
--</SBLK>--

insert into
--<II>--
	dbo.fc_Master_Lookup_TermLoan
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.ApplicationId as ApplicationId,
		b.ArrearsAdvanceId as ArrearsAdvanceId,
		b.DocumentationFeeId as DocumentationFeeId,
		b.FinanceTypeId as FinanceTypeId,
		b.FirstPaymentDate as FirstPaymentDate,
		b.LoanAmount as LoanAmount,
		b.Motivation as Motivation,
		b.PaymentFrequencyId as PaymentFrequencyId,
		b.Rate as Rate,
		b.RateAuthorisation as RateAuthorisation,
		b.RateTypeId as RateTypeId,
		b.RateWithinPolicy as RateWithinPolicy,
		b.RepaymentPeriod as RepaymentPeriod,
		b.SourceOfRepayment as SourceOfRepayment,
		b.TermLoanPurposeId as TermLoanPurposeId,
		b.srv as srv
--</S>--
from	
--<F>--
		dbo.TermLoan a 
		left outer join 
		dbo.Union_m_TermLoan b

on		a.applicationid = b.applicationid and 
		a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_TermLoan
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.DocumentationFeeChange') is not null 
begin drop table dbo.DocumentationFeeChange end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid
--</S>--
into	
--<I>--
	dbo.DocumentationFeeChange
--</I>--

from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_DocumentationFeeChange b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and 
		1=2

group by a.originalapplicationid, 
	     a.srv 
--</F>--
--</SBLK>--

insert into
--<II>--
	dbo.DocumentationFeeChange
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid
--</S>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_DocumentationFeeChange b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 

group by a.originalapplicationid, 
	     a.srv 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.DocumentationFeeChange
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------happy--
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_DocumentationFeeChange') is not null 
begin drop table dbo.fc_Master_Lookup_DocumentationFeeChange end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid , 
		b.ApplicationId as ApplicationId,
		b.ApprovedDocumentationFee as ApprovedDocumentationFee,
		b.Comments as Comments,
		b.[Date] as [Date],
		b.DocumentationFeeAdjustmentId as DocumentationFeeAdjustmentId,
		b.DocumentationFeeDecision as DocumentationFeeDecision,
		b.DocumentationFeeOptionId as DocumenationFeeOptionId,
		b.NewDocumentationFee as NewDocumentationFee,
		b.OptionDecision as OptionDecision,
		b.Updated as Updated,
		b.[User] as [User],
		b.srv as srv

--</S>--
into	
--<I>--
		dbo.fc_Master_Lookup_DocumentationFeeChange
--</I>--

from	
--<F>--
		dbo.DocumentationFeeChange a 
		left outer join 
		dbo.Union_m_DocumentationFeeChange b 

on		a.applicationid = b.applicationid and 
		a.srv = b.srv 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2
--</F>--
--</SBLK>--

insert into
--<II>--
		dbo.fc_Master_Lookup_DocumentationFeeChange
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid , 
		b.ApplicationId as ApplicationId,
		b.ApprovedDocumentationFee as ApprovedDocumentationFee,
		b.Comments as Comments,
		b.[Date] as [Date],
		b.DocumentationFeeAdjustmentId as DocumentationFeeAdjustmentId,
		b.DocumentationFeeDecision as DocumentationFeeDecision,
		b.DocumentationFeeOptionId as DocumenationFeeOptionId,
		b.NewDocumentationFee as NewDocumentationFee,
		b.OptionDecision as OptionDecision,
		b.Updated as Updated,
		b.[User] as [User],
		b.srv as srv

--</S>--
from	
--<F>--
		dbo.DocumentationFeeChange a 
		left outer join 
		dbo.Union_m_DocumentationFeeChange b 

on		a.applicationid = b.applicationid and 
		a.srv = b.srv 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_DocumentationFeeChange
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.CreditTurnover') is not null 
begin drop table dbo.CreditTurnover end
--</M>--
select
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid
--</S>--
into	
--<I>--
	dbo.CreditTurnover
--</I>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_CreditTurnover b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2

group by a.originalapplicationid, 
		 a.srv 
--</F>--
--</SBLK>--

insert into
--<II>--
	dbo.CreditTurnover
--</II>--
select
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid
--</S>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_CreditTurnover b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 

group by a.originalapplicationid, 
		 a.srv 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.CreditTurnover
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_CreditTurnover') is not null 
begin drop table dbo.fc_Master_Lookup_CreditTurnover end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.ApplicantRepositoryId as ApplicantRepositoryId,
		b.ApplicationId as ApplicationId,
		b.Credits as Credits,
		b.Credits1 as Credits1,
		b.Credits2 as Credits2,
		b.Credits3 as Credits3,
		b.Credits4 as Credits4,
		b.Credits5 as Credits5,
		b.Deposit as Deposit,
		b.Deposit1 as Deposit1,
		b.Deposit2 as Deposit2,
		b.Deposit3 as Deposit3,
		b.Deposit4 as Deposit4,
		b.Deposit5 as Deposit5,
		b.Income as Income,
		b.Income1 as Income1,
		b.Income2 as Income2,
		b.Income3 as Income3,
		b.Income4 as Income4,
		b.Income5 as Income5,
		b.OpEx as OpEx ,
		b.OpEx1 as OpEx1,
		b.OpEx2 as OpEx2,
		b.OpEx3 as OpEx3,
		b.OpEx4 as OpEx4,
		b.OpEx5 as OpEx5,
		b.Ops as Ops,
		b.Ops1 as Ops1,
		b.Ops2 as Ops2,
		b.Ops3 as Ops3,
		b.Ops4 as Ops4,
		b.Ops5 as Ops5,
		b.SurplusDeficit as SurplusDeficit,
		b.SurplusDeficit1 as SurplusDeficit1,
		b.SurplusDeficit2 as SurplusDeficit2,
		b.SurplusDeficit3 as SurplusDeficit3,
		b.SurplusDeficit4 as SurplusDeficit4,
		b.SurplusDeficit5 as SurplusDeficit5,
		b.Turnover as Turnover,
		b.srv as srv 
--</S>--
into	
--<I>--
		dbo.fc_Master_Lookup_CreditTurnover
--</I>--

from	
--<F>--
		dbo.CreditTurnover a 
		left outer join 
		dbo.Union_m_CreditTurnover b

on	a.applicationid = b.applicationid and 
	a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2
--</F>--
--</SBLK>--

insert into
--<II>--
		dbo.fc_Master_Lookup_CreditTurnover
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.ApplicantRepositoryId as ApplicantRepositoryId,
		b.ApplicationId as ApplicationId,
		b.Credits as Credits,
		b.Credits1 as Credits1,
		b.Credits2 as Credits2,
		b.Credits3 as Credits3,
		b.Credits4 as Credits4,
		b.Credits5 as Credits5,
		b.Deposit as Deposit,
		b.Deposit1 as Deposit1,
		b.Deposit2 as Deposit2,
		b.Deposit3 as Deposit3,
		b.Deposit4 as Deposit4,
		b.Deposit5 as Deposit5,
		b.Income as Income,
		b.Income1 as Income1,
		b.Income2 as Income2,
		b.Income3 as Income3,
		b.Income4 as Income4,
		b.Income5 as Income5,
		b.OpEx as OpEx ,
		b.OpEx1 as OpEx1,
		b.OpEx2 as OpEx2,
		b.OpEx3 as OpEx3,
		b.OpEx4 as OpEx4,
		b.OpEx5 as OpEx5,
		b.Ops as Ops,
		b.Ops1 as Ops1,
		b.Ops2 as Ops2,
		b.Ops3 as Ops3,
		b.Ops4 as Ops4,
		b.Ops5 as Ops5,
		b.SurplusDeficit as SurplusDeficit,
		b.SurplusDeficit1 as SurplusDeficit1,
		b.SurplusDeficit2 as SurplusDeficit2,
		b.SurplusDeficit3 as SurplusDeficit3,
		b.SurplusDeficit4 as SurplusDeficit4,
		b.SurplusDeficit5 as SurplusDeficit5,
		b.Turnover as Turnover,
		b.srv as srv 
--</S>--
from	
--<F>--
		dbo.CreditTurnover a 
		left outer join 
		dbo.Union_m_CreditTurnover b

on	a.applicationid = b.applicationid and 
	a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_CreditTurnover
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.AccountDetails') is not null 
begin drop table dbo.AccountDetails end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid
--</S>--
into	
--<I>--
	dbo.AccountDetails
--</I>--

from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_AccountDetails b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2

group by a.originalapplicationid, 
		 a.srv 
--</F>--
--</SBLK>--

insert into
--<II>--
	dbo.AccountDetails
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid
--</S>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_AccountDetails b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 

group by a.originalapplicationid, 
		 a.srv 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.AccountDetails
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------

--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_AccountDetails') is not null 
begin drop table dbo.fc_Master_Lookup_AccountDetails end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.ApplicationId as ApplicationId,
		b.BrandId as BrandId,
		b.CANumber as CANumber,
		b.CIS as CIS,
		b.Dcar as Dcar,
		b.HasPersonalLoan as HasPersonalLoan,
		b.HLNumber as HLNumber,
		b.IsBusinessApplicaition as IsBusinessApplication,
		b.IsPrincipleApplication as IsPrincipleApplication,
		b.MarketSector as MarketSector,
		b.MarketSegment as MarketSegment,
		b.NVFNumber as NVFNumber,
		b.PersonalLoanNumber as PersonalLoanNumber ,
		b.SANumber as SANumber,
		b.TaxiTaxNumber as TaxiTaxNumber,
		b.srv as srv 
--</S>--
into
--<I>--
	dbo.fc_Master_Lookup_AccountDetails
--</I>--
from	
--<F>--
		dbo.AccountDetails a 
		left outer join 
		dbo.Union_m_AccountDetails b 

on		a.applicationid = b.applicationid and
		a.srv = b.srv  

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2
--</F>--
--</SBLK>--

insert into
--<II>--
	dbo.fc_Master_Lookup_AccountDetails
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.ApplicationId as ApplicationId,
		b.BrandId as BrandId,
		b.CANumber as CANumber,
		b.CIS as CIS,
		b.Dcar as Dcar,
		b.HasPersonalLoan as HasPersonalLoan,
		b.HLNumber as HLNumber,
		b.IsBusinessApplicaition as IsBusinessApplication,
		b.IsPrincipleApplication as IsPrincipleApplication,
		b.MarketSector as MarketSector,
		b.MarketSegment as MarketSegment,
		b.NVFNumber as NVFNumber,
		b.PersonalLoanNumber as PersonalLoanNumber ,
		b.SANumber as SANumber,
		b.TaxiTaxNumber as TaxiTaxNumber,
		b.srv as srv 
--</S>--
from	
--<F>--
		dbo.AccountDetails a 
		left outer join 
		dbo.Union_m_AccountDetails b 

on		a.applicationid = b.applicationid and
		a.srv = b.srv  

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_AccountDetails
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.DecisionUpdated') is not null 
begin drop table dbo.DecisionUpdated end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid, 
		min(b.applicationid) as Initialapplicationid, 
		max(decisionupdateid) as Finaldecisionupdateid,
		min(decisionupdateid) as Initialdecisionupdateid
--</S>--
into	
--<I>--
	dbo.DecisionUpdated
--</I>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_DecisionUpdate b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2

group by a.originalapplicationid, 
		 a.srv 
--</F>--
--</SBLK>--

insert into
--<II>--
	dbo.DecisionUpdated
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicationid) as applicationid, 
		min(b.applicationid) as Initialapplicationid, 
		max(decisionupdateid) as Finaldecisionupdateid,
		min(decisionupdateid) as Initialdecisionupdateid
--</S>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join 
		dbo.Union_m_DecisionUpdate b 

on		a.applicationid = b.applicationid 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 

group by a.originalapplicationid, 
		 a.srv 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.DecisionUpdated
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Initial_Decision') is not null 
begin drop table dbo.Initial_Decision end
--</M>--
select 
--<S>--
	   a.originalapplicationid as originalapplicationid, 
	   a.srv as srv, 
	   b.DecisionUser as initialDecisionUser, 
	   b.AdditionalLimit as InitialAdditionalLimit,
	   b.FinalAppealDecisionId as InitialFinalAppealDecisionid, 
	   b.TotalApprovedLimit as InitialTotalApprovedlimit, 
	   b.OverrideReasonId as InitialOverrideReasonId, 
	   b.Rate as InitialRate
--</S>--
into 
--<I>--
	dbo.Initial_Decision
--</I>--
from 
--<F>--
	 dbo.DecisionUpdated a
	 left outer join 
	 Union_m_DecisionUpdate b

on	 a.Initialapplicationid = b.applicationid and 
	 a.srv = b.srv and 
	 InitialDecisionupdateid = DecisionUpdateId

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2
--</F>--
--</SBLK>--

insert into
--<II>--	
	dbo.Initial_Decision
--</II>--	
select 
--<S>--
	   a.originalapplicationid as originalapplicationid, 
	   a.srv as srv, 
	   b.DecisionUser as initialDecisionUser, 
	   b.AdditionalLimit as InitialAdditionalLimit,
	   b.FinalAppealDecisionId as InitialFinalAppealDecisionid, 
	   b.TotalApprovedLimit as InitialTotalApprovedlimit, 
	   b.OverrideReasonId as InitialOverrideReasonId, 
	   b.Rate as InitialRate
--</S>--
from 
--<F>--
	 dbo.DecisionUpdated a
	 left outer join 
	 Union_m_DecisionUpdate b

on	 a.Initialapplicationid = b.applicationid and 
	 a.srv = b.srv and 
	 InitialDecisionupdateid = DecisionUpdateId

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Initial_Decision
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------

--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Final_Decision') is not null 
begin drop table dbo.Final_Decision end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		b.DecisionUser as FinalDecisionUser, 
		b.AdditionalLimit as FinalAdditionalLimit,
		b.FinalAppealDecisionId as FinalFinalAppealDecisionid, 
		b.TotalApprovedLimit as FinalTotalApprovedlimit, 
		b.OverrideReasonId as FinalOverrideReasonId, 
		b.Rate as FinalRate
--</S>--
into	
--<I>--
	dbo.Final_Decision
--</I>--
from	
--<F>--
		dbo.DecisionUpdated a
		left outer join 
		Union_m_DecisionUpdate b

on		a.applicationid = b.applicationid and 
		a.srv = b.srv and 
		FinalDecisionupdateid = DecisionUpdateId

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2
--</F>--
--</SBLK>--

insert into
--<II>--
 	dbo.Final_Decision
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		b.DecisionUser as FinalDecisionUser, 
		b.AdditionalLimit as FinalAdditionalLimit,
		b.FinalAppealDecisionId as FinalFinalAppealDecisionid, 
		b.TotalApprovedLimit as FinalTotalApprovedlimit, 
		b.OverrideReasonId as FinalOverrideReasonId, 
		b.Rate as FinalRate
--</S>--
from	
--<F>--
		dbo.DecisionUpdated a
		left outer join 
		Union_m_DecisionUpdate b

on		a.applicationid = b.applicationid and 
		a.srv = b.srv and 
		FinalDecisionupdateid = DecisionUpdateId

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Final_Decision
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Final_DecisionUpdate') is not null 
begin drop table dbo.Final_DecisionUpdate end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid,
		a.srv as srv,
		a.initialDecisionUser as InitialDecisionUser,
		a.InitialAdditionalLimit as InitialAdditionalLimit,
		a.InitialFinalAppealDecisionid as InitialFinalAppealDecisionid,
		a.InitialTotalApprovedlimit as InitialTotalApprovedlimit,
		a.InitialOverrideReasonId as InitialOverrideReasonId,
		a.InitialRate as InitialRate ,
		b.finalDecisionUser as finalDecisionUser, 
		b.FinalAdditionalLimit as FinalAdditionalLimit,
		b.FinalFinalAppealDecisionid as FinalFinalAppealDecisionid, 
		b.FinalTotalApprovedlimit as FinalTotalApprovedlimit,
		b.FinalOverrideReasonId as FinalOverrideReasonId, 
		b.FinalRate as FinalRate
--</S>--
into	
--<I>--
	dbo.Final_DecisionUpdate
--</I>--
from	
--<F>--
		dbo.Initial_Decision a
		left outer join 
		dbo.Final_Decision b

on		a.originalapplicationid = b.originalapplicationid and 
		a.srv = b.srv

where	a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2
--</F>--
--</SBLK>--
insert into
--<II>--
	dbo.Final_DecisionUpdate
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid,
		a.srv as srv,
		a.initialDecisionUser as InitialDecisionUser,
		a.InitialAdditionalLimit as InitialAdditionalLimit,
		a.InitialFinalAppealDecisionid as InitialFinalAppealDecisionid,
		a.InitialTotalApprovedlimit as InitialTotalApprovedlimit,
		a.InitialOverrideReasonId as InitialOverrideReasonId,
		a.InitialRate as InitialRate ,
		b.finalDecisionUser as finalDecisionUser, 
		b.FinalAdditionalLimit as FinalAdditionalLimit,
		b.FinalFinalAppealDecisionid as FinalFinalAppealDecisionid, 
		b.FinalTotalApprovedlimit as FinalTotalApprovedlimit,
		b.FinalOverrideReasonId as FinalOverrideReasonId, 
		b.FinalRate as FinalRate
--</S>--
from	
--<F>--
		dbo.Initial_Decision a
		left outer join 
		dbo.Final_Decision b

on		a.originalapplicationid = b.originalapplicationid and 
		a.srv = b.srv

where	a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Final_DecisionUpdate
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_Final_DecisionUpdate') is not null 
begin drop table dbo.fc_Master_Lookup_Final_DecisionUpdate end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.AdditionalLimit as AdditionalLimit,
		b.AppealAllowed as AppealAllowed,
		b.AppealAssessmentComments as AppealAssessmentComments,
		b.AppealCounter as AppealCounter,
		b.ApplicationId as ApplicationId,
		b.BalloonAmount as BalloonAmount,
		b.BalloonDate as BalloonDate,
		b.ChangeApplicationAllowed as ChangeApplicationAllowed,
		b.ChangeTOB as ChangeTOB,
		b.CurrentLimit as CurrentLimit,
		b.DecisionDate as DecisionDate,
		b.DecisionUpdateId as DecisionUpdateId,
		b.DecisionUser as DecisionUser,
		b.DeclineReasonId as DeclineReasonId,
		b.DepositAmount as DepositAmount,
		b.DepositPercent as DepositPercent,
		b.FinalAppealDecisionId as FinalAppealDecisionId,
		b.FinalVerificationOutcomeId as FinalVerificationOutcomeId,
		b.Installment as Installment,
		b.Interest as Interst,
		b.IsKhulaApplication as IsKhulaApplication,
		b.LastPaymentDate as LastPaymentDate,
		b.LoanToValue as LoanToValue,
		b.MonthlyReductionAmount as MonthlyReductionAmount,
		b.MustFacilityReduce as MustFacilityReduce,
		b.NCAApplicability as NCAApplicability,
		b.OnceOffExpiryDate as OnceOffExpiryDate,
		b.OverrideReasonId as OverrideReasonId,
		b.PaymentFrequencyId as PaymentFrequencyId,
		b.Prime as Prime,
		b.PrimeFactor as PrimeFactor,
		b.PurchasePrice as PurchasePrice,
		b.RaisingFee as RaisingFee,
		b.Rate as Rate,
		b.RateTypeId as RateTypeId,
		b.RecaptureReasonId as RecaptureTeasonId,
		b.ReduceBackTo as ReduceBackTo,
		b.ReductionAmount as ReductionAmount,
		b.ReductionStartDate as ReductionStartDate,
		b.ReductionTypeId as ReductionTypeId,
		b.Term as Term,
		b.TotalApprovedLimit as TotalApprovedLimit,
		b.srv as srv,
		b.IsIncomeValidated as IsIncomeValidated,
		c.InitialDecisionUser as InitialDecisionUser , 
		c.InitialAdditionalLimit as InitialAdditionalLimit,
		c.InitialFinalAppealDecisionid as InitialFinalAppealDecisionid, 
		c.InitialTotalApprovedlimit as InitialTotalApprovedlimit,
		c.InitialOverrideReasonId as InitialOverrideReasonId, 
		c.InitialRate as InitialRate,
		c.finalDecisionUser as FinalDecisionUser, 
		c.FinalAdditionalLimit as FinalAdditionalLimit ,
		c.FinalFinalAppealDecisionid as FinalFinalAppealDecisionid, 
		c.FinalTotalApprovedlimit as FinalTotalApprovedlimit ,
		c.FinalOverrideReasonId as FinalOverrideReasonId, 
		c.FinalRate as FinalRate
--</S>--
into	
--<I>--
	dbo.fc_Master_Lookup_Final_DecisionUpdate
--</I>--
from	
--<F>--
		dbo.DecisionUpdated a 
		left outer join 
		dbo.Union_m_DecisionUpdate b 

on		a.applicationid = b.applicationid and 
		a.Finaldecisionupdateid = b.decisionupdateid and
		a.srv = b.srv 
		left outer join 
		dbo.Final_DecisionUpdate c

on		a.originalapplicationid = c.originalapplicationid and 
		a.srv = c.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2
--</F>--
--</SBLK>--
insert into
--<II>--
	dbo.fc_Master_Lookup_Final_DecisionUpdate
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.AdditionalLimit as AdditionalLimit,
		b.AppealAllowed as AppealAllowed,
		b.AppealAssessmentComments as AppealAssessmentComments,
		b.AppealCounter as AppealCounter,
		b.ApplicationId as ApplicationId,
		b.BalloonAmount as BalloonAmount,
		b.BalloonDate as BalloonDate,
		b.ChangeApplicationAllowed as ChangeApplicationAllowed,
		b.ChangeTOB as ChangeTOB,
		b.CurrentLimit as CurrentLimit,
		b.DecisionDate as DecisionDate,
		b.DecisionUpdateId as DecisionUpdateId,
		b.DecisionUser as DecisionUser,
		b.DeclineReasonId as DeclineReasonId,
		b.DepositAmount as DepositAmount,
		b.DepositPercent as DepositPercent,
		b.FinalAppealDecisionId as FinalAppealDecisionId,
		b.FinalVerificationOutcomeId as FinalVerificationOutcomeId,
		b.Installment as Installment,
		b.Interest as Interst,
		b.IsKhulaApplication as IsKhulaApplication,
		b.LastPaymentDate as LastPaymentDate,
		b.LoanToValue as LoanToValue,
		b.MonthlyReductionAmount as MonthlyReductionAmount,
		b.MustFacilityReduce as MustFacilityReduce,
		b.NCAApplicability as NCAApplicability,
		b.OnceOffExpiryDate as OnceOffExpiryDate,
		b.OverrideReasonId as OverrideReasonId,
		b.PaymentFrequencyId as PaymentFrequencyId,
		b.Prime as Prime,
		b.PrimeFactor as PrimeFactor,
		b.PurchasePrice as PurchasePrice,
		b.RaisingFee as RaisingFee,
		b.Rate as Rate,
		b.RateTypeId as RateTypeId,
		b.RecaptureReasonId as RecaptureTeasonId,
		b.ReduceBackTo as ReduceBackTo,
		b.ReductionAmount as ReductionAmount,
		b.ReductionStartDate as ReductionStartDate,
		b.ReductionTypeId as ReductionTypeId,
		b.Term as Term,
		b.TotalApprovedLimit as TotalApprovedLimit,
		b.srv as srv,
		b.IsIncomeValidated as IsIncomeValidated,
		c.InitialDecisionUser as InitialDecisionUser , 
		c.InitialAdditionalLimit as InitialAdditionalLimit,
		c.InitialFinalAppealDecisionid as InitialFinalAppealDecisionid, 
		c.InitialTotalApprovedlimit as InitialTotalApprovedlimit,
		c.InitialOverrideReasonId as InitialOverrideReasonId, 
		c.InitialRate as InitialRate,
		c.finalDecisionUser as FinalDecisionUser, 
		c.FinalAdditionalLimit as FinalAdditionalLimit ,
		c.FinalFinalAppealDecisionid as FinalFinalAppealDecisionid, 
		c.FinalTotalApprovedlimit as FinalTotalApprovedlimit ,
		c.FinalOverrideReasonId as FinalOverrideReasonId, 
		c.FinalRate as FinalRate
--</S>--
from	
--<F>--
		dbo.DecisionUpdated a 
		left outer join 
		dbo.Union_m_DecisionUpdate b 

on		a.applicationid = b.applicationid and 
		a.Finaldecisionupdateid = b.decisionupdateid and
		a.srv = b.srv 
		left outer join 
		dbo.Final_DecisionUpdate c

on		a.originalapplicationid = c.originalapplicationid and 
		a.srv = c.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_Final_DecisionUpdate
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------

--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.SumAll_Union_Application_1') is not null 
begin drop table dbo.SumAll_Union_Application_1 end
--</M>--
select 
--<S>-- 
		OriginalApplicationId as OriginalApplicationId, 
		ApplicationChannelId as ApplicationChannelId,
		srv as srv,
		sum(dbo.fn_fc_SumTakenUp(TakenUp)) as SumTakenUp,
		sum(dbo.fn_fc_SumIsVehicle(IsVehicle)) as SumIsVehicle,
		sum(dbo.fn_fc_SumHasBureauConsent(HasBureauConsent)) as SumHasBureauConsent, 
		sum(dbo.fn_fc_SumIsPreApproval(IsPreApproval)) as SumIsPreApproval,
		sum(dbo.fn_fc_SumApplicantRepositoryId(ApplicantRepositoryId)) as SumApplicantRepositoryId,
		sum(dbo.fn_fc_SumisTaxiDeal(TaxiAssociationId,isTaxiDeal)) as SumisTaxiDeal,
		sum(dbo.fn_fc_SumIsFrozen(IsFrozen)) as SumIsFrozen,
		sum(dbo.fn_fc_SumIsSBSApplicationcase(IsSBSApplication)) as SumSBSApplication,
		min(DateCaptured) as Min_DateCaptured,
		max(DateCaptured) as Max_DateCaptured,
		dbo.fn_fc_DaysCapturing(DateCaptured) as DaysCapturing, 
		max(applicationid) as applicationid, 
		min(applicationid) as min_applicationid
	
--</S>--
into	
--<I>--
	dbo.SumAll_Union_Application_1
--</I>--
FROM	
--<F>--
	dbo.Union_m_Application

where	1=2

GROUP BY OriginalApplicationId, 
		 srv, 
		 ApplicationChannelId
		 
		 

HAVING	(OriginalApplicationId <> 0)
--</F>--
--</SBLK>--
insert into
--<II>--
	dbo.SumAll_Union_Application_1
--</II>--
select 
--<S>-- 
		OriginalApplicationId as OriginalApplicationId, 
		ApplicationChannelId as ApplicationChannelId,
		srv as srv,
		sum(dbo.fn_fc_SumTakenUp(TakenUp)) as SumTakenUp,
		sum(dbo.fn_fc_SumIsVehicle(IsVehicle)) as SumIsVehicle,
		sum(dbo.fn_fc_SumHasBureauConsent(HasBureauConsent)) as SumHasBureauConsent, 
		sum(dbo.fn_fc_SumIsPreApproval(IsPreApproval)) as SumIsPreApproval,
		sum(dbo.fn_fc_SumApplicantRepositoryId(ApplicantRepositoryId)) as SumApplicantRepositoryId,
		sum(dbo.fn_fc_SumisTaxiDeal(TaxiAssociationId,isTaxiDeal)) as SumisTaxiDeal,
		sum(dbo.fn_fc_SumIsFrozen(IsFrozen)) as SumIsFrozen,
		sum(dbo.fn_fc_SumIsSBSApplicationcase(IsSBSApplication)) as SumSBSApplication,
		min(DateCaptured) as Min_DateCaptured,
		max(DateCaptured) as Max_DateCaptured,
		dbo.fn_fc_DaysCapturing(DateCaptured) as DaysCapturing, 
		max(applicationid) as applicationid, 
		min(applicationid) as min_applicationid
--</S>--
FROM	
--<F>--
	dbo.Union_m_Application

GROUP BY OriginalApplicationId, 
		 srv, 
		 ApplicationChannelId
		 

HAVING	(OriginalApplicationId <> 0)
--</F>--
--</BLK>--
--------------------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Updated_Union_Application_1') is not null 
begin drop table dbo.Updated_Union_Application_1 end
--</M>--
Select
--<S>-- 
		OriginalApplicationId as OriginalApplicationId, 
		ApplicationChannelId as ApplicationChannelId,
		srv as srv,		
		dbo.fn_fc_TakenUp(SumTakenUp) as TakenUp,
		dbo.fn_fc_IsVehicle(SumIsVehicle) as isVehcile,
		dbo.fn_fc_HasBureauConsent(SumHasBureauConsent) as HasBureauConsent, 
		dbo.fn_fc_IsPreApproval(SumIsPreApproval) as IsPreApproval,
		dbo.fn_fc_ApplicantRepositoryId(SumApplicantRepositoryId) as ApplicantRepositoryId,
		dbo.fn_fc_isTaxiDeal(SumisTaxiDeal) as isTaxiDeal,
		dbo.fn_fc_IsFrozen(SumIsFrozen) as IsFrozen,
		dbo.fn_fc_IsSBSApplication(SumSBSApplication) as SBSApplication,
		Min_DateCaptured as Min_DateCaptured,
		Max_DateCaptured as Max_DateCaptured,
		DaysCapturing as DaysCapturing, 
		applicationid as applicationid, 
		min_applicationid as min_applicationid
--</S>--
into	
--<I>--
	dbo.Updated_Union_Application_1
--</I>--
FROM	
--<F>--
	dbo.SumAll_Union_Application_1

where	1=2

GROUP BY OriginalApplicationId, 
		 srv, 
		 ApplicationChannelId,
		 dbo.fn_fc_TakenUp(SumTakenUp),
		 dbo.fn_fc_IsVehicle(SumIsVehicle),
		 dbo.fn_fc_HasBureauConsent(SumHasBureauConsent), 
		 dbo.fn_fc_IsPreApproval(SumIsPreApproval),
		 dbo.fn_fc_ApplicantRepositoryId(SumApplicantRepositoryId),
		 dbo.fn_fc_isTaxiDeal(SumisTaxiDeal),
		 dbo.fn_fc_IsFrozen(SumIsFrozen),
		 dbo.fn_fc_IsSBSApplication(SumSBSApplication),
		 Min_DateCaptured,
		 Max_DateCaptured,
		 DaysCapturing, 
		 applicationid, 
		 min_applicationid

HAVING	(OriginalApplicationId <> 0)

--</F>--
--</SBLK>--
insert into
--<II>--
	dbo.Updated_Union_Application_1
--</II>--
Select
--<S>-- 
		OriginalApplicationId as OriginalApplicationId, 
		ApplicationChannelId as ApplicationChannelId,
		srv as srv,		
		dbo.fn_fc_TakenUp(SumTakenUp) as TakenUp,
		dbo.fn_fc_IsVehicle(SumIsVehicle) as isVehcile,
		dbo.fn_fc_HasBureauConsent(SumHasBureauConsent) as HasBureauConsent, 
		dbo.fn_fc_IsPreApproval(SumIsPreApproval) as IsPreApproval,
		dbo.fn_fc_ApplicantRepositoryId(SumApplicantRepositoryId) as ApplicantRepositoryId,
		dbo.fn_fc_isTaxiDeal(SumisTaxiDeal) as isTaxiDeal,
		dbo.fn_fc_IsFrozen(SumIsFrozen) as IsFrozen,
		dbo.fn_fc_IsSBSApplication(SumSBSApplication) as SBSApplication,
		Min_DateCaptured as Min_DateCaptured,
		Max_DateCaptured as Max_DateCaptured,
		DaysCapturing as DaysCapturing, 
		applicationid as applicationid, 
		min_applicationid as min_applicationid
--</S>--
FROM	
--<F>--
	dbo.SumAll_Union_Application_1
 
		
GROUP BY OriginalApplicationId, 
		 srv, 
		 ApplicationChannelId,
		 dbo.fn_fc_TakenUp(SumTakenUp),
		 dbo.fn_fc_IsVehicle(SumIsVehicle),
		 dbo.fn_fc_HasBureauConsent(SumHasBureauConsent), 
		 dbo.fn_fc_IsPreApproval(SumIsPreApproval),
		 dbo.fn_fc_ApplicantRepositoryId(SumApplicantRepositoryId),
		 dbo.fn_fc_isTaxiDeal(SumisTaxiDeal),
		 dbo.fn_fc_IsFrozen(SumIsFrozen),
		 dbo.fn_fc_IsSBSApplication(SumSBSApplication),
		 Min_DateCaptured,
		 Max_DateCaptured,
		 DaysCapturing, 
		 applicationid, 
		 min_applicationid

HAVING	(OriginalApplicationId <> 0)

--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Updated_Union_Application_1
([ApplicationId],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
---------------------------------------------------------------------------------------------------------------- 
--<BLK>--
--<SBLK>--
----STRT_CMT7_(GET THE INITIAL CAPTURER)----END_CMT7
--<M>--
if object_id('dbo.First_Capturer') is not null 
begin drop table dbo.First_Capturer end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		b.[user] as First_Capturer, 
		b.datecaptured as datecaptured
--</S>--

into	
--<I>--
		dbo.First_Capturer
--</I>--

from	
--<F>--
		dbo.Updated_Union_Application_1 a
		left outer join 
		dbo.Union_m_Application b

on		a.originalapplicationid = b.originalapplicationid and 
		a.srv = b.srv and 
		a.min_applicationid = b.applicationid and 
		a.min_Datecaptured = b.datecaptured

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0  and
		1=2

group by a.originalapplicationid, 
		 a.srv, 
	     b.[user], 
		 b.datecaptured 

--</F>--
--</SBLK>--
insert into
--<II>--
		dbo.First_Capturer
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		b.[user] as First_Capturer, 
		b.datecaptured as datecaptured
--</S>--
from	
--<F>--
		dbo.Updated_Union_Application_1 a
		left outer join 
		dbo.Union_m_Application b

on		a.originalapplicationid = b.originalapplicationid and 
		a.srv = b.srv and 
		a.min_applicationid = b.applicationid and 
		a.min_Datecaptured = b.datecaptured

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0  

group by a.originalapplicationid, 
		 a.srv, 
	     b.[user], 
		 b.datecaptured 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.First_Capturer
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
---------------------------------------------------------------------------------------------------------------- 
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Last_Capturer') is not null 
begin drop table dbo.Last_Capturer end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		b.[user] as last_Capturer, 
		b.datecaptured as datecaptured
--</S>--
into	
--<I>--
	dbo.Last_Capturer
--</I>--

from	
--<F>--
		dbo.Updated_Union_Application_1 a
		left outer join 
		dbo.Union_m_Application b

on		a.originalapplicationid = b.originalapplicationid and 
		a.srv = b.srv and 
		a.applicationid = b.applicationid and 
		a.max_Datecaptured = b.datecaptured

where	b.Applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2

group by a.originalapplicationid, 
		 a.srv, 
		 b.[user], 
		 b.datecaptured 

--</F>--
--</SBLK>--
Insert into
--<II>--
	dbo.Last_Capturer
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		b.[user] as last_Capturer, 
		b.datecaptured as datecaptured
--</S>--
from	
--<F>--
		dbo.Updated_Union_Application_1 a
		left outer join 
		dbo.Union_m_Application b

on		a.originalapplicationid = b.originalapplicationid and 
		a.srv = b.srv and 
		a.applicationid = b.applicationid and 
		a.max_Datecaptured = b.datecaptured

where	b.Applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 

group by a.originalapplicationid, 
		 a.srv, 
		 b.[user], 
		 b.datecaptured 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Last_Capturer
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------happy2--- 
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Capturer') is not null 
begin drop table dbo.Capturer end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv,
		a.first_capturer as first_capturer, 
		b.last_capturer as last_capturer
--</S>--
into	 
--<I>--
		dbo.Capturer
--</I>--

from	
--<F>--
		dbo.First_Capturer a
		left outer join 
		dbo.Last_Capturer b


on		a.originalapplicationid = b.originalapplicationid and 
		a.srv = b.srv

where	a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2

group by a.originalapplicationid, 
		 a.srv,
		 a.first_capturer, 
		 b.last_capturer
--</F>--
--</SBLK>--
Insert into
--<II>--
		dbo.Capturer
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv,
		a.first_capturer as first_capturer, 
		b.last_capturer as last_capturer
--</S>--
from	
--<F>--
		dbo.First_Capturer a
		left outer join 
		dbo.Last_Capturer b

on		a.originalapplicationid = b.originalapplicationid and 
		a.srv = b.srv

where	a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 

group by a.originalapplicationid, 
		 a.srv,
		 a.first_capturer, 
		 b.last_capturer
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Capturer
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
---------------------------------------------------------------------------------------------------------------- 
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.union_Application_2') is not null 
begin drop table dbo.union_Application_2 end
--</M>--
SELECT	
--<S>--
		OriginalApplicationId as OriginalApplicationId, 
		srv as srv, 
		max(DateCaptured) AS date1, 
		max(applicationid) as Applicationid
--</S>--
into	
--<I>--
		dbo.union_Application_2
--</I>--

FROM	
--<F>--
		dbo.Union_m_Application

where	applicationid is not null and 
		originalapplicationid is not null and 
		originalapplicationid <> 0 and
		1=2 

GROUP BY OriginalApplicationId, 
		 srv

HAVING	(OriginalApplicationId <> 0)
--</F>--
--</SBLK>--
insert into
--<II>--
		dbo.union_Application_2
--</II>--
SELECT	
--<S>--
		OriginalApplicationId as OriginalApplicationId, 
		srv as srv, 
		max(DateCaptured) AS date1, 
		max(applicationid) as Applicationid
--</S>--
FROM	
--<F>--
		dbo.Union_m_Application

where	applicationid is not null and 
		originalapplicationid is not null and 
		originalapplicationid <> 0 

GROUP BY OriginalApplicationId, 
		 srv

HAVING	(OriginalApplicationId <> 0)
--</F>--


CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.union_Application_2
([OriginalApplicationId],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.union_Application_3a') is not null 
begin drop table dbo.union_Application_3a end
--</M>--
SELECT	
--<S>--
		a.OriginalApplicationId as OriginalApplicationId, 
		a.srv as srv, 
		a.date1 as date1, 
		a.applicationid as applicationid, 
		b.RequestId AS requestid_last,
		b.AssetTypeId AS assettypeid_last, 
		b.ProductId AS ProductId_last
--</S>--

into	
--<I>--
		dbo.union_Application_3a
--</I>--

FROM	
--<F>--
		dbo.union_Application_2 a
		LEFT OUTER JOIN 
		dbo.Union_m_Application b

ON		a.OriginalApplicationId = b.OriginalApplicationId AND 
		a.date1 = b.DateCaptured and 
		a.applicationid = b.applicationid and 
		a.srv = b.srv

where	a.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2 
group by a.OriginalApplicationId, 
		 a.srv, 
		 a.date1,
		 a.applicationid, 
		 b.RequestId, 
		 b.AssetTypeId, 
		 b.ProductId 
--</F>--
--</SBLK>--
insert into
--<II>--
		dbo.union_Application_3a
--</II>--
SELECT	
--<S>--
		a.OriginalApplicationId as OriginalApplicationId, 
		a.srv as srv, 
		a.date1 as date1, 
		a.applicationid as applicationid, 
		b.RequestId AS requestid_last,
		b.AssetTypeId AS assettypeid_last, 
		b.ProductId AS ProductId_last
--</S>--
FROM	
--<F>--
		dbo.union_Application_2 a
		LEFT OUTER JOIN 
		dbo.Union_m_Application b

ON		a.OriginalApplicationId = b.OriginalApplicationId AND 
		a.date1 = b.DateCaptured and 
		a.applicationid = b.applicationid and 
		a.srv = b.srv

where	a.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 

group by a.OriginalApplicationId, 
		 a.srv, 
		 a.date1,
		 a.applicationid, 
		 b.RequestId, 
		 b.AssetTypeId, 
		 b.ProductId 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.union_Application_3a
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.union_Application_3') is not null 
begin drop table dbo.union_Application_3 end
--</M>--
select	
--<S>--	
		OriginalApplicationId as OriginalApplicationId, 
		srv as srv, 
		date1 as date1, 
		applicationid as applicationid, 
		max(RequestId_last) as RequestId_last, 
		max(AssetTypeId_last) as AssetTypeId_last, 
		max(ProductId_last) as ProductId_last
--</S>--
into	
--<I>--
		dbo.union_Application_3
--</I>--
from	
--<F>--
		dbo.union_Application_3a

where	1=2

group by OriginalApplicationId, 
		 srv, 
		 date1, 
		 applicationid

--</F>--
--</SBLK>--
insert into
--<II>--
		dbo.union_Application_3
--</II>--
select	
--<S>--	
		OriginalApplicationId as OriginalApplicationId, 
		srv as srv, 
		date1 as date1, 
		applicationid as applicationid,  
		max(RequestId_last) as RequestId_last, 
		max(AssetTypeId_last) as AssetTypeId_last, 
		max(ProductId_last) as ProductId_last
--</S>--
from	
--<F>--
		dbo.union_Application_3a

group by OriginalApplicationId, 
		 srv, 
		 date1, 
		 applicationid
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.union_Application_3
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.union_Application_4') is not null 
begin drop table dbo.union_Application_4 end
--</M>--
SELECT	
--<S>--
		b.OriginalApplicationId as OriginalApplicationId, 
		b.srv as srv, 
		MAX(b.DateCaptured) AS date2
--</S>--
into	
--<I>--
	dbo.union_Application_4
--</I>--
FROM	
--<F>--
		dbo.union_Application_2 a
		LEFT OUTER JOIN 
		dbo.Union_m_Application b

ON		a.OriginalApplicationId = b.OriginalApplicationId AND 
		a.date1 <> b.DateCaptured and 
		a.srv = b.srv

where 1=2

GROUP BY b.OriginalApplicationId, 
		 b.srv

having	 b.originalapplicationid is not null 
--</F>--
--</SBLK>--
insert into
--<II>--
	dbo.union_Application_4
--</II>--
SELECT	
--<S>--
		b.OriginalApplicationId as OriginalApplicationId, 
		b.srv as srv, 
		MAX(b.DateCaptured) AS date2
--</S>--
FROM	
--<F>--
		dbo.union_Application_2 a
		LEFT OUTER JOIN 
		dbo.Union_m_Application b

ON		a.OriginalApplicationId = b.OriginalApplicationId AND 
		a.date1 <> b.DateCaptured and 
		a.srv = b.srv

GROUP BY b.OriginalApplicationId, 
		 b.srv

having	 b.originalapplicationid is not null 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.union_Application_4
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------happy3-----

--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.union_Application_5') is not null 
begin drop table dbo.union_Application_5 end
--</M>--
SELECT	
--<S>--
		a.OriginalApplicationId as OriginalApplicationId, 
		a.srv as srv, 
		a.date2 as date2, 
		max(b.RequestId) AS requestid_prior, 
		max(b.AssetTypeId) AS assettypeid_prior, 
		max(b.ProductId) AS ProductId_prior,
		max([user]) as max_user
--</S>--
into	
--<I>--
	dbo.union_Application_5
--</I>--
FROM	
--<F>--
		dbo.union_Application_4 a
		LEFT OUTER JOIN 
		dbo.Union_m_Application b 

ON		a.OriginalApplicationId = b.OriginalApplicationId AND 
		a.date2 = b.DateCaptured and 
		a.srv = b.srv

where	a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2

group by a.OriginalApplicationId, 
		 a.srv, 
		 a.date2 
--</F>--
--</SBLK>--
insert into
--<II>--
	dbo.union_Application_5
--</II>--
SELECT	
--<S>--
		a.OriginalApplicationId as OriginalApplicationId, 
		a.srv as srv, 
		a.date2 as date2, 
		max(b.RequestId) AS requestid_prior, 
		max(b.AssetTypeId) AS assettypeid_prior, 
		max(b.ProductId) AS ProductId_prior,
		max([user]) as max_user
--</S>--
FROM	
--<F>--
		dbo.union_Application_4 a
		LEFT OUTER JOIN 
		dbo.Union_m_Application b 

ON		a.OriginalApplicationId = b.OriginalApplicationId AND 
		a.date2 = b.DateCaptured and 
		a.srv = b.srv

where	a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 

group by a.OriginalApplicationId, 
		 a.srv, 
		 a.date2 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.union_Application_5
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_Application_Fact') is not null 
begin drop table dbo.fc_Master_Lookup_Application_Fact end
--</M>--
select	
--<S>--
		a.OriginalApplicationId as OriginalApplicationId,
		a.ApplicationChannelId as ApplicationChannelId,
		a.srv as srv,
		a.TakenUp as TakenUp,
		a.isVehcile as isVehicle,
		a.HasBureauConsent as HasBureauConsent,
		a.IsPreApproval as IsPreApproval,
		a.ApplicantRepositoryId as ApplicantRepositoryId,
		a.IsTaxiDeal as IsTaxiDeal,
		a.IsFrozen as IsFrozen,
		a.SBSApplication as IsSBSApplication,
		a.Min_DateCaptured as Min_DateCaptured,
		a.Max_DateCaptured as Max_DateCaptured,
		a.DaysCapturing as DaysCapturing,
		a.applicationid as applicationid,
		a.min_applicationid as min_applicationid, 
		d.first_capturer as first_capturer, 
		d.last_capturer as last_capturer, 
		b.requestid_last as requestid_last, 
		b.assettypeid_last as assettypeid_last, 
		b.ProductId_last as ProductId_last, 
		dbo.fn_fc_CRequestid_prior(c.requestid_prior) as requestid_prior, 
		dbo.fn_fc_C_assettypeid_prior(c.assettypeid_prior) as assettypeid_prior, 
		dbo.fn_fc_CProductId_prior(c.ProductId_prior) as ProductId_prior
--</S>--
into	
--<I>--
	dbo.fc_Master_Lookup_Application_Fact
--</I>--
from	
--<F>--
		dbo.Updated_Union_Application_1 a
		right outer join 
		dbo.union_Application_3 b

on		a.originalapplicationid = b.originalapplicationid and 
		a.srv = b.srv and 
		a.applicationid = b.applicationid
		left outer join 
		dbo.union_Application_5 c

on		a.originalapplicationid = c.originalapplicationid and 
		a.srv = c.srv
		left outer join 
		dbo.Capturer d

on		a.originalapplicationid = d.originalapplicationid and 
		a.srv = d.srv

where	1=2
--</F>--
--</SBLK>--
insert into
--<II>--
	dbo.fc_Master_Lookup_Application_Fact
--</II>--
select	
--<S>--
		a.OriginalApplicationId as OriginalApplicationId,
		a.ApplicationChannelId as ApplicationChannelId,
		a.srv as srv,
		a.TakenUp as TakenUp,
		a.isVehcile as isVehicle,
		a.HasBureauConsent as HasBureauConsent,
		a.IsPreApproval as IsPreApproval,
		a.ApplicantRepositoryId as ApplicantRepositoryId,
		a.IsTaxiDeal as IsTaxiDeal,
		a.IsFrozen as IsFrozen,
		a.SBSApplication as IsSBSApplication,
		a.Min_DateCaptured as Min_DateCaptured,
		a.Max_DateCaptured as Max_DateCaptured,
		a.DaysCapturing as DaysCapturing,
		a.applicationid as applicationid,
		a.min_applicationid as min_applicationid, 
		d.first_capturer as first_capturer, 
		d.last_capturer as last_capturer, 
		b.requestid_last as requestid_last, 
		b.assettypeid_last as assettypeid_last, 
		b.ProductId_last as ProductId_last, 
		dbo.fn_fc_CRequestid_prior(c.requestid_prior) as requestid_prior, 
		dbo.fn_fc_C_assettypeid_prior(c.assettypeid_prior) as assettypeid_prior, 
		dbo.fn_fc_CProductId_prior(c.ProductId_prior) as ProductId_prior
--</S>--
from	
--<F>--
		dbo.Updated_Union_Application_1 a
		right outer join 
		dbo.union_Application_3 b

on		a.originalapplicationid = b.originalapplicationid and 
		a.srv = b.srv and 
		a.applicationid = b.applicationid
		left outer join 
		dbo.union_Application_5 c

on		a.originalapplicationid = c.originalapplicationid and 
		a.srv = c.srv
		left outer join 
		dbo.Capturer d

on		a.originalapplicationid = d.originalapplicationid and 
		a.srv = d.srv

--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_Application_Fact
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--

--<M>--
if object_id('dbo.Union_AppealRelook_1') is not null 
begin drop table dbo.Union_AppealRelook_1 end
--</M>--
SELECT	
--<S>--
		dbo.Union_m_Application.OriginalApplicationId as OriginalApplicationId , 
		dbo.Union_m_Application.srv as srv,
		dbo.Union_m_Application.AppealCount as AppealCount, 
		dbo.Union_m_Application.RelookCount as RelookCount, 
		dbo.Union_m_Application.ApplicationCounter as ApplicationCounter, 
		dbo.Union_m_AppealRelook.ReasonId as ReasonId, 
		dbo.fn_fc_AppealReasonId1(dbo.Union_m_Application.AppealCount,dbo.Union_m_AppealRelook.ReasonId) as AReason1,
		dbo.fn_fc_AppealReasonId2(dbo.Union_m_Application.AppealCount,dbo.Union_m_AppealRelook.ReasonId) as AReason2,
		dbo.fn_fc_AppealReasonId3(dbo.Union_m_Application.AppealCount,dbo.Union_m_AppealRelook.ReasonId) as AReason3,
		dbo.fn_fc_AppealReasonId4(dbo.Union_m_Application.AppealCount,dbo.Union_m_AppealRelook.ReasonId) as AReason4,
		dbo.fn_fc_AppealReasonId5(dbo.Union_m_Application.AppealCount,dbo.Union_m_AppealRelook.ReasonId) as AReason5,
		dbo.fn_fc_AppealReasonIdX(dbo.Union_m_Application.AppealCount) as AReasonX
--</S>--
into	
--<I>--
		dbo.Union_AppealRelook_1
--</I>--
FROM	
--<F>--
		dbo.Union_m_AppealRelook 
		inner join 
		dbo.Union_m_Application

on		dbo.Union_m_AppealRelook.ApplicationId = dbo.Union_m_Application.ApplicationId and 
		dbo.Union_m_AppealRelook.srv = dbo.Union_m_Application.srv

WHERE	(dbo.Union_m_Application.RelookCount = 0)and
		1=2
--</F>--
--</SBLK>--
Insert into
--<II>--   
		dbo.Union_AppealRelook_1
--</II>--
SELECT	
--<S>--
		dbo.Union_m_Application.OriginalApplicationId as OriginalApplicationId , 
		dbo.Union_m_Application.srv as srv,
		dbo.Union_m_Application.AppealCount as AppealCount, 
		dbo.Union_m_Application.RelookCount as RelookCount, 
		dbo.Union_m_Application.ApplicationCounter as ApplicationCounter, 
		dbo.Union_m_AppealRelook.ReasonId as ReasonId, 
		dbo.fn_fc_AppealReasonId1(dbo.Union_m_Application.AppealCount,dbo.Union_m_AppealRelook.ReasonId) as AReason1,
		dbo.fn_fc_AppealReasonId2(dbo.Union_m_Application.AppealCount,dbo.Union_m_AppealRelook.ReasonId) as AReason2,
		dbo.fn_fc_AppealReasonId3(dbo.Union_m_Application.AppealCount,dbo.Union_m_AppealRelook.ReasonId) as AReason3,
		dbo.fn_fc_AppealReasonId4(dbo.Union_m_Application.AppealCount,dbo.Union_m_AppealRelook.ReasonId) as AReason4,
		dbo.fn_fc_AppealReasonId5(dbo.Union_m_Application.AppealCount,dbo.Union_m_AppealRelook.ReasonId) as AReason5,
		dbo.fn_fc_AppealReasonIdX(dbo.Union_m_Application.AppealCount) as AReasonX
--</S>--
FROM	
--<F>--
		dbo.Union_m_AppealRelook 
		inner join 
		dbo.Union_m_Application

on		dbo.Union_m_AppealRelook.ApplicationId = dbo.Union_m_Application.ApplicationId and 
		dbo.Union_m_AppealRelook.srv = dbo.Union_m_Application.srv

WHERE	(dbo.Union_m_Application.RelookCount = 0)
--</F>--
CREATE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Union_AppealRelook_1
([OriginalApplicationId],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Union_AppealRelook_1b') is not null 
begin drop table dbo.Union_AppealRelook_1b end
--</M>--
select	
--<S>--
		OriginalApplicationId as OriginalApplicationId, 
		srv as srv, 
		AppealCount as AppealCount, 
		AReason1 as AReason1, 
		AReason2 as AReason2, 
		AReason3 as AReason3, 
		AReason4 as AReason4, 
		AReason5 as AReason5,
		AReasonX as AReasonX 
--</S>--
into	
--<I>--
		dbo.Union_AppealRelook_1b
--</I>--
from	
--<F>--
		dbo.Union_AppealRelook_1

where	1=2

group by OriginalApplicationId, 
		 srv, 
		 AReason1 , 
		 AReason2 , 
		 AReason3 , 
	     AReason4 , 
		 AReason5 , 
		 AReasonX , 
		 AppealCount
--</F>--
--</SBLK>--
insert into
--<II>--
		dbo.Union_AppealRelook_1b
--</II>--
select	
--<S>--
		OriginalApplicationId as OriginalApplicationId, 
		srv as srv, 
		AppealCount as AppealCount, 
		AReason1 as AReason1, 
		AReason2 as AReason2, 
		AReason3 as AReason3, 
		AReason4 as AReason4, 
		AReason5 as AReason5,
		AReasonX as AReasonX 
--</S>--
from	
--<F>--
		dbo.Union_AppealRelook_1

group by OriginalApplicationId, 
		 srv, 
		 AReason1 , 
		 AReason2 , 
		 AReason3 , 
	     AReason4 , 
		 AReason5 , 
		 AReasonX , 
		 AppealCount
--</F>--
CREATE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Union_AppealRelook_1b
([OriginalApplicationId],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Union_AppealRelook_2') is not null 
begin drop table dbo.Union_AppealRelook_2 end
--</M>--
SELECT	
--<S>--
		OriginalApplicationId as OriginalApplicationId, 
		srv as srv, 
		max(AReason1) AS AReason1, 
		max(AReason2) AS AReason2, 
		max(AReason3) AS AReason3, 
		max(AReason4) AS AReason4, 
		max(AReason5) AS AReason5, 
		max(AReasonX) AS AReasonX, 
		max(AppealCount) AS max_AppealCount
--</S>--
into	
--<I>--
	dbo.Union_AppealRelook_2
--</I>--
FROM	
--<F>--
	dbo.Union_AppealRelook_1b

where 1=2

GROUP BY OriginalApplicationId, 
		 srv
--</F>--
--</SBLK>--
insert into
--<II>--
	dbo.Union_AppealRelook_2
--</II>--
SELECT	
--<S>--
		OriginalApplicationId as OriginalApplicationId, 
		srv as srv, 
		max(AReason1) AS AReason1, 
		max(AReason2) AS AReason2, 
		max(AReason3) AS AReason3, 
		max(AReason4) AS AReason4, 
		max(AReason5) AS AReason5, 
		max(AReasonX) AS AReasonX, 
		max(AppealCount) AS max_AppealCount
--</S>--
FROM	
--<F>--
	dbo.Union_AppealRelook_1b

GROUP BY OriginalApplicationId, 
		 srv
--</F>--

CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Union_AppealRelook_2
([OriginalApplicationId],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Union_AppealRelook_3') is not null 
begin drop table dbo.Union_AppealRelook_3 end
--</M>--
SELECT	
--<S>--
		b.OriginalApplicationId as OriginalApplicationId, 
		b.srv as srv, 
		SUM(CAST(a.AppealRelookStaffware AS int)) AS Appealrelookstaffware_sum, 
		SUM(CAST(a.ChangeApplicationInformation AS int)) AS ChangeApplicationinformation_sum, 
		SUM(CAST(a.ChangeSecurityInformation AS int)) AS ChangeSecurityinformation_sum, 
		SUM(CAST(a.ChangeAssetInformation AS int)) AS ChangeAssetinformation_sum, 
		SUM(CAST(a.ChangeDealerInformation AS int)) AS ChangeDealerinformation_sum, 
		SUM(CAST(a.ChangePaymentMediumDetails AS int)) AS ChangePaymentMediumDetails_sum, 
		SUM(CAST(a.ChangeSupportingDocumetsDetails AS int)) AS ChangeSupportingDocumetsdetails_sum, 
		SUM(CAST(a.ChangeLoanInformation AS int)) AS ChangeLoaninformation_sum, 
		SUM(a.RelookCount) AS Relook_sum
--</S>--
into	
--<I>--
	dbo.Union_AppealRelook_3
--</I>--
FROM	
--<F>--
		dbo.Union_m_AppealRelook a
		INNER JOIN 
		dbo.Union_m_Application b 

ON		a.ApplicationId = b.ApplicationId
		and a.srv = b.srv

where	1=2

GROUP BY b.OriginalApplicationId, b.srv
--</F>--
--</SBLK>--
insert into
--<II>--
	dbo.Union_AppealRelook_3
--</II>--
SELECT	
--<S>--
		b.OriginalApplicationId as OriginalApplicationId, 
		b.srv as srv, 
		SUM(CAST(a.AppealRelookStaffware AS int)) AS Appealrelookstaffware_sum, 
		SUM(CAST(a.ChangeApplicationInformation AS int)) AS ChangeApplicationinformation_sum, 
		SUM(CAST(a.ChangeSecurityInformation AS int)) AS ChangeSecurityinformation_sum, 
		SUM(CAST(a.ChangeAssetInformation AS int)) AS ChangeAssetinformation_sum, 
		SUM(CAST(a.ChangeDealerInformation AS int)) AS ChangeDealerinformation_sum, 
		SUM(CAST(a.ChangePaymentMediumDetails AS int)) AS ChangePaymentMediumDetails_sum, 
		SUM(CAST(a.ChangeSupportingDocumetsDetails AS int)) AS ChangeSupportingDocumetsdetails_sum, 
		SUM(CAST(a.ChangeLoanInformation AS int)) AS ChangeLoaninformation_sum, 
		SUM(a.RelookCount) AS Relook_sum
--</S>--
FROM	
--<F>--
		dbo.Union_m_AppealRelook a
		INNER JOIN 
		dbo.Union_m_Application b 

ON		a.ApplicationId = b.ApplicationId
		and a.srv = b.srv

GROUP BY b.OriginalApplicationId, b.srv
--</F>--

CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Union_AppealRelook_3
([OriginalApplicationId],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------happy4----
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_AppealReLook_Fact') is not null 
begin drop table dbo.fc_Master_AppealReLook_Fact end
--</M>--
SELECT	
--<S>--
		a.OriginalApplicationId as OriginalApplicationId,
		a.srv as srv, 
		dbo.fn_fc_Sum_Relook_sum(a.Relook_sum) as Relook_sum,
		dbo.fn_fc_Max_AppealCount(b.max_AppealCount) as Max_AppealCount , 
		dbo.fn_fc_AppealReLookAReason1(b.AReason1) as AReason1,
		dbo.fn_fc_AppealReLookAReason2(b.AReason2) as AReason2,
		dbo.fn_fc_AppealReLookAReason3(b.AReason3) as AReason3,
		dbo.fn_fc_AppealReLookAReason4(b.AReason4) as AReason4,
		dbo.fn_fc_AppealReLookAReason5(b.AReason5) as AReason5,
		dbo.fn_fc_AppealReLookAReasonX(b.AReasonX) as AReasonX,
		dbo.fn_fc_ChangeApplicationinfo_Sum(a.ChangeApplicationinformation_Sum) as ChangeApplicationinformation_sum, 
		dbo.fn_fc_ChangeSecurityinfo_Sum(a.ChangeSecurityinformation_Sum) as ChangeSecurityinformation_sum , 
		dbo.fn_fc_ChangeAssetinfo_Sum(a.ChangeAssetinformation_Sum) as ChangeAssetinformation_sum, 
		dbo.fn_fc_ChangeDealerinfo_Sum(a.ChangeDealerinformation_Sum) as ChangeDealerinformation_sum ,
		dbo.fn_fc_ChangePaymentMediumDetails_Sum(a.ChangePaymentMediumDetails_Sum) as ChangePaymentMediumDetails_sum, 
		dbo.fn_fc_ChangeSupportingDocumetsdetails_Sum(a.ChangeSupportingDocumetsdetails_Sum) as ChangeSupportingDocumetsdetails_sum ,
		dbo.fn_fc_ChangeLoaninformation_Sum(a.ChangeLoaninformation_Sum) as ChangeLoaninformation_sum, 
		dbo.fn_fc_Appealrelookstaffware_Sum(a.Appealrelookstaffware_Sum) as Appealrelookstaffware_sum
--</S>--
into	
--<I>--
	dbo.fc_Master_AppealReLook_Fact
--</I>--
FROM	
--<F>--
		dbo.Union_AppealRelook_3 a 
		LEFT OUTER JOIN 
		dbo.Union_AppealRelook_2 b 

ON		a.OriginalApplicationId = b.OriginalApplicationId and 
		a.srv = b.srv

where	1=2
--</F>--
--</SBLK>--

insert into
--<II>--
	dbo.fc_Master_AppealReLook_Fact
--</II>--
SELECT	
--<S>--
		a.OriginalApplicationId as OriginalApplicationId,
		a.srv as srv, 
		dbo.fn_fc_Sum_Relook_sum(a.Relook_sum) as Relook_sum,
		dbo.fn_fc_Max_AppealCount(b.max_AppealCount) as Max_AppealCount , 
		dbo.fn_fc_AppealReLookAReason1(b.AReason1) as AReason1,
		dbo.fn_fc_AppealReLookAReason2(b.AReason2) as AReason2,
		dbo.fn_fc_AppealReLookAReason3(b.AReason3) as AReason3,
		dbo.fn_fc_AppealReLookAReason4(b.AReason4) as AReason4,
		dbo.fn_fc_AppealReLookAReason5(b.AReason5) as AReason5,
		dbo.fn_fc_AppealReLookAReasonX(b.AReasonX) as AReasonX,
		dbo.fn_fc_ChangeApplicationinfo_Sum(a.ChangeApplicationinformation_Sum) as ChangeApplicationinformation_sum, 
		dbo.fn_fc_ChangeSecurityinfo_Sum(a.ChangeSecurityinformation_Sum) as ChangeSecurityinformation_sum , 
		dbo.fn_fc_ChangeAssetinfo_Sum(a.ChangeAssetinformation_Sum) as ChangeAssetinformation_sum, 
		dbo.fn_fc_ChangeDealerinfo_Sum(a.ChangeDealerinformation_Sum) as ChangeDealerinformation_sum ,
		dbo.fn_fc_ChangePaymentMediumDetails_Sum(a.ChangePaymentMediumDetails_Sum) as ChangePaymentMediumDetails_sum, 
		dbo.fn_fc_ChangeSupportingDocumetsdetails_Sum(a.ChangeSupportingDocumetsdetails_Sum) as ChangeSupportingDocumetsdetails_sum ,
		dbo.fn_fc_ChangeLoaninformation_Sum(a.ChangeLoaninformation_Sum) as ChangeLoaninformation_sum, 
		dbo.fn_fc_Appealrelookstaffware_Sum(a.Appealrelookstaffware_Sum) as Appealrelookstaffware_sum
--</S>--
FROM	
--<F>--
		dbo.Union_AppealRelook_3 a 
		LEFT OUTER JOIN 
		dbo.Union_AppealRelook_2 b 

ON		a.OriginalApplicationId = b.OriginalApplicationId and 
		a.srv = b.srv

--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_AppealReLook_Fact
([OriginalApplicationId],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
----STRT_CMT8_(NOTE: dbo.fc_Master_Lookup_ApplicationState_Fact)----END_CMT8
----STRT_CMT9_(NOTE: Asses_sub updated to check for the last occurence of assessment comments for Approve, Refer and Decline)----END_CMT9
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Union_ApplicationState_1') is not null 
begin drop table dbo.Union_ApplicationState_1 end
--</M>--
SELECT	
--<S>--
		b.OriginalApplicationId as OriginalApplicationId, 
		b.srv as srv, 
		dbo.fn_fc_Asses_sub(AssessmentComments) as Asses_sub,
		b.DateCaptured as DateCaptured, 
		a.ApplicationId as ApplicationId, 
		a.StaffwareCase as StaffwareCase, 
		a.ApplicationStateId as ApplicationStateId, 
		a.AssessmentComments as AssessmentComments, 
		a.StaffwareStateId as StaffwareStateId, 
		a.TransactStateId as TransactStateId, 
		a.StaffwareSubmitDate as StaffwareSubmitDate, 
		a.TransactSubmitDate as TransactSubmitDate, 
		a.ApplicationDecisionId as ApplicationDecisionId, 
		a.PreAppealStateId as PreAppealStateId
--</S>--
into	
--<I>--
		dbo.Union_ApplicationState_1
--</I>--
from	
--<F>--
		dbo.Union_m_ApplicationState a
		left outer join 
		dbo.Union_m_Application b

on		a.ApplicationId = b.ApplicationId and 
		a.srv = b.srv  

where	1=2
--</F>--
--</SBLK>--
insert into
--<II>--
		dbo.Union_ApplicationState_1
--</II>--
SELECT	
--<S>--
		b.OriginalApplicationId as OriginalApplicationId, 
		b.srv as srv, 
		dbo.fn_fc_Asses_sub(AssessmentComments) as Asses_sub,
		b.DateCaptured as DateCaptured, 
		a.ApplicationId as ApplicationId, 
		a.StaffwareCase as StaffwareCase, 
		a.ApplicationStateId as ApplicationStateId, 
		a.AssessmentComments as AssessmentComments, 
		a.StaffwareStateId as StaffwareStateId, 
		a.TransactStateId as TransactStateId, 
		a.StaffwareSubmitDate as StaffwareSubmitDate, 
		a.TransactSubmitDate as TransactSubmitDate, 
		a.ApplicationDecisionId as ApplicationDecisionId, 
		a.PreAppealStateId as PreAppealStateId
--</S>--
from	
--<F>--
		dbo.Union_m_ApplicationState a
		left outer join 
		dbo.Union_m_Application b

on		a.ApplicationId = b.ApplicationId and 
		a.srv = b.srv  
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Union_ApplicationState_1
([ApplicationId],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------happy5---

--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Union_ApplicationState_2') is not null 
begin drop table dbo.Union_ApplicationState_2 end
--</M>--
select	
--<S>--
		originalapplicationid as originalapplicationid , 
		srv a srv, 
		max(applicationid) as applicationid_max, 
		max(TransactSubmitDate) as TransactSubmitDate_max, 
		max(DateCaptured) as DateCaptured,
		min(applicationid) as first_application, 
		max(applicationid) as Last_application
--</S>--
into	
--<I>--
		dbo.Union_ApplicationState_2
--</I>--
from	
--<F>--
		dbo.Union_ApplicationState_1

where	1=2

group by originalapplicationid, 
		 srv
--</F>--
--</SBLK>--

insert into
--<II>--
	dbo.Union_ApplicationState_2
--</II>--
select	
--<S>--
		originalapplicationid as originalapplicationid, 
		srv as srv, 
		max(applicationid) as applicationid_max, 
		max(TransactSubmitDate) as TransactSubmitDate_max, 
		max(DateCaptured) as DateCaptured,
		min(applicationid) as first_application, 
		max(applicationid) as Last_application
--</S>--
from	
--<F>--
		dbo.Union_ApplicationState_1

group by originalapplicationid, 
		 srv
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Union_ApplicationState_2
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.first_applicationstateid') is not null 
begin drop table dbo.first_applicationstateid end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		a.applicationstateid as First_ApplicationStateid
--</S>--
into 
--<I>--
	dbo.first_applicationstateid
--</I>--
from 
--<F>--
	 dbo.Union_ApplicationState_1 a,
	 dbo.Union_ApplicationState_2 b

where a.originalapplicationid = b.originalapplicationid and 
	  a.srv = b.srv and 
	  a.applicationid = b.first_application and 
	  1=2
--</F>--
--</SBLK>--
insert into
--<II>--
	dbo.first_applicationstateid
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		a.applicationstateid as First_ApplicationStateid
--</S>--
from 
--<F>--
	 dbo.Union_ApplicationState_1 a,
	 dbo.Union_ApplicationState_2 b

where a.originalapplicationid = b.originalapplicationid and 
	  a.srv = b.srv and 
	  a.applicationid = b.first_application 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.first_applicationstateid
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--

--<M>--
if object_id('dbo.last_applicationstateid') is not null 
begin drop table dbo.last_applicationstateid end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		a.applicationstateid as last_ApplicationStateid
--</S>--
into	
--<I>--
		dbo.last_applicationstateid
--</I>--
from    
--<F>--
		dbo.Union_ApplicationState_1 a, 
	 	dbo.Union_ApplicationState_2 b

where a.originalapplicationid = b.originalapplicationid and 
	  a.srv = b.srv and 
	  a.applicationid = b.last_application and
	  1=2
--</F>--
--</SBLK>--
insert into
--<II>--
		dbo.last_applicationstateid
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		a.applicationstateid as last_ApplicationStateid
--</S>--
from    
--<F>--
		dbo.Union_ApplicationState_1 a, 
	 	dbo.Union_ApplicationState_2 b

where a.originalapplicationid = b.originalapplicationid and 
	  a.srv = b.srv and 
	  a.applicationid = b.last_application 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.last_applicationstateid
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.applicationstateid_s') is not null 
begin drop table dbo.applicationstateid_s end
--</M>--
select  
--<S>--
		a.originalapplicationid as originalapplicationid,
		a.srv as srv, 
		a.First_ApplicationStateid as First_ApplicationStateid, 
		b.last_ApplicationStateid as last_applicationStateid 
--</S>--
into 
--<I>--
	dbo.applicationstateid_s
--</I>--

from 
--<F>--
	 dbo.first_applicationstateid a
	 left outer join 
	 dbo.last_applicationstateid b

on  a.originalapplicationid = b.originalapplicationid and 
	a.srv = b.srv

where	1=2

--</F>--
--</SBLK>--
insert into
--<II>--
	dbo.applicationstateid_s
--</II>--
select  
--<S>--
		a.originalapplicationid as originalapplicationid,
		a.srv as srv, 
		a.First_ApplicationStateid as First_ApplicationStateid, 
		b.last_ApplicationStateid as last_applicationStateid 
--</S>--

from 
--<F>--
	 dbo.first_applicationstateid a
	 left outer join 
	 dbo.last_applicationstateid b

on  a.originalapplicationid = b.originalapplicationid and 
	a.srv = b.srv
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.applicationstateid_s
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--

--<M>--
if object_id('dbo.fc_Lookup_ApplicationState_Fact') is not null 
begin drop table dbo.fc_Lookup_ApplicationState_Fact end
--</M>--
select	
--<S>--
		a.OriginalApplicationId as OriginalApplicationId,
		a.srv as srv,
		a.Asses_sub as Asses_sub,
		a.DateCaptured as DateCaptured,
		a.ApplicationId as ApplicationId,
		a.StaffwareCase as StaffwareCase,
		a.ApplicationStateId as ApplicationStateId,
		a.AssessmentComments as AssessmentComments,
		a.StaffwareStateId as StaffwareStateId,
		a.TransactStateId as TransactStateId,
		a.StaffwareSubmitDate as StaffwareSubmitDate,
		a.TransactSubmitDate as TransactSubmitDate,
		a.ApplicationDecisionId as ApplicationDecisionId,
		a.PreAppealStateId as  PreAppealStateId
--</S>--
into	
--<I>--
		dbo.fc_Lookup_ApplicationState_Fact
--</I>--

from	
--<F>--
		dbo.Union_ApplicationState_1 a 
		inner join 
		dbo.Union_ApplicationState_2 b 

on		a.originalapplicationid = b.originalapplicationid and 
		a.applicationid = b.applicationid_max and 
		a.srv = b.srv

where	1=2
--</F>--
--</SBLK>--
insert into
--<II>--
		dbo.fc_Lookup_ApplicationState_Fact
--</II>--
select	
--<S>--
		a.OriginalApplicationId as OriginalApplicationId,
		a.srv as srv,
		a.Asses_sub as Asses_sub,
		a.DateCaptured as DateCaptured,
		a.ApplicationId as ApplicationId,
		a.StaffwareCase as StaffwareCase,
		a.ApplicationStateId as ApplicationStateId,
		a.AssessmentComments as AssessmentComments,
		a.StaffwareStateId as StaffwareStateId,
		a.TransactStateId as TransactStateId,
		a.StaffwareSubmitDate as StaffwareSubmitDate,
		a.TransactSubmitDate as TransactSubmitDate,
		a.ApplicationDecisionId as ApplicationDecisionId,
		a.PreAppealStateId as  PreAppealStateId 
--</S>--
from	
--<F>--
		dbo.Union_ApplicationState_1 a 
		inner join 
		dbo.Union_ApplicationState_2 b 

on		a.originalapplicationid = b.originalapplicationid and 
		a.applicationid = b.applicationid_max and 
		a.srv = b.srv


--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Lookup_ApplicationState_Fact
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_ApplicationState_Fact') is not null 
begin drop table dbo.fc_Master_Lookup_ApplicationState_Fact end
--</M>--
select  
--<S>--
		a.OriginalApplicationId as OriginalApplicationId,
		a.srv as srv,
		a.Asses_sub as Asses_sub,
		a.DateCaptured as DateCaptured,
		a.ApplicationId as ApplicationId,
		a.StaffwareCase as StaffwareCase,
		a.ApplicationStateId as ApplicationStateId,
		a.AssessmentComments as AssessmentComments,
		a.StaffwareStateId as StaffwareStateId,
		a.TransactStateId as TransactStateId,
		a.StaffwareSubmitDate as StaffwareSubmitDate,
		a.TransactSubmitDate as TransactSubmitDate,
		a.ApplicationDecisionId as ApplicationDecisionId ,
		a.PreAppealStateId as PreAppealStateId,
		b.First_ApplicationStateid as First_ApplicationStateid , 
		b.last_ApplicationStateid as last_ApplicationStateid
--</S>--
into 
--<I>--
	dbo.fc_Master_Lookup_ApplicationState_Fact
--</I>--

from 
--<F>--
	 dbo.fc_Lookup_ApplicationState_Fact a
	 left outer join 
	 dbo.applicationstateid_s b

on	a.originalapplicationid = b.originalapplicationid and 
	a.srv = b.srv

where 1=2
--</F>--
--</SBLK>--
insert into
--<II>--
	dbo.fc_Master_Lookup_ApplicationState_Fact
--</II>--
select  
--<S>--
		a.OriginalApplicationId as OriginalApplicationId,
		a.srv as srv,
		a.Asses_sub as Asses_sub,
		a.DateCaptured as DateCaptured,
		a.ApplicationId as ApplicationId,
		a.StaffwareCase as StaffwareCase,
		a.ApplicationStateId as ApplicationStateId,
		a.AssessmentComments as AssessmentComments,
		a.StaffwareStateId as StaffwareStateId,
		a.TransactStateId as TransactStateId,
		a.StaffwareSubmitDate as StaffwareSubmitDate,
		a.TransactSubmitDate as TransactSubmitDate,
		a.ApplicationDecisionId as ApplicationDecisionId ,
		a.PreAppealStateId as PreAppealStateId,
		b.First_ApplicationStateid as First_ApplicationStateid , 
		b.last_ApplicationStateid as last_ApplicationStateid
--</S>--
from 
--<F>--
	 dbo.fc_Lookup_ApplicationState_Fact a
	 left outer join 
	 dbo.applicationstateid_s b

on	a.originalapplicationid = b.originalapplicationid and 
	a.srv = b.srv
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_ApplicationState_Fact
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------

--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Final_Main_App') is not null 
begin drop table dbo.Final_Main_App end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv,
		max(b.applicantid) as applicantid, 
		max(b.applicationid) as applicationid
--</S>--
into	
--<I>--
	dbo.Final_Main_App
--</I>--
from	
--<F>--
		dbo.Union_m_application a 
		left outer join 
		dbo.Union_m_applicant b 

on		a.applicationid = b.applicationid and 
		a.srv = b.srv

where	b.applicationrelationid = 1 and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2

group by a.originalapplicationid,
		 a.srv

--</F>--
--</SBLK>--
insert into
--<II>--
	dbo.Final_Main_App
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv,
		max(b.applicantid) as applicantid, 
		max(b.applicationid) as applicationid
--</S>--
from	
--<F>--
		dbo.Union_m_application a 
		left outer join 
		dbo.Union_m_applicant b 

on		a.applicationid = b.applicationid and 
		a.srv = b.srv

where	b.applicationrelationid = 1 and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 

group by a.originalapplicationid,
		 a.srv

--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Final_Main_App
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------happy6--

--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Final_Business_App') is not null 
begin drop table dbo.Final_Business_App end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicantid) as applicantid, 
		max(b.applicationid) as applicationid 
--</S>--
into	
--<I>--
	dbo.Final_Business_App
--</I>--

from	
--<F>--
		dbo.Union_m_application a
		left outer join
		dbo.Union_m_applicant b 

on		a.applicationid = b.applicationid and 
		a.srv = b.srv

where	b.applicationrelationid = 7 and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and 
		originalapplicationid not in (select originalapplicationid from dbo.Final_Main_App)and
		1=2

group by a.originalapplicationid, a.srv

--</F>--
--</SBLK>--
insert into
--<II>--
	dbo.Final_Business_App
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		max(b.applicantid) as applicantid, 
		max(b.applicationid) as applicationid 
--</S>--
from	
--<F>--
		dbo.Union_m_application a
		left outer join
		dbo.Union_m_applicant b 

on		a.applicationid = b.applicationid and 
		a.srv = b.srv
where	b.applicationrelationid = 7 and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and 
		originalapplicationid not in (select originalapplicationid from dbo.Final_Main_App)
		
group by a.originalapplicationid, 
		 a.srv
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Final_Business_App
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.Last_App_Found') is not null 
begin drop table dbo.Last_App_Found end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		b.applicationid as applicationid, 
		max(b.applicantid) as applicantid 
--</S>--
into	
--<I>--
	dbo.Last_App_Found
--</I>--

from	
--<F>--
		dbo.Union_m_application a 
		left outer join 
		dbo.Union_m_applicant b 

on		a.applicationid = b.applicationid and 
		a.srv = b.srv

where	a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and 
		a.originalapplicationid not in (select originalapplicationid from dbo.Final_Main_App) and 
		a.originalapplicationid not in (select originalapplicationid from dbo.Final_Business_App)and
		1=2

group by a.originalapplicationid, 
		 a.srv, 
		 b.applicationid 

having	(max(b.applicantid)) is not null
--</F>--
--</SBLK>--
insert into
--<II>--
	dbo.Last_App_Found
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as srv, 
		b.applicationid as applicationid, 
		max(b.applicantid) as applicantid 
--</S>--
from	
--<F>--
		dbo.Union_m_application a 
		left outer join 
		dbo.Union_m_applicant b 

on		a.applicationid = b.applicationid and 
		a.srv = b.srv

where	a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and 
		a.originalapplicationid not in (select originalapplicationid from dbo.Final_Main_App) and 
		a.originalapplicationid not in (select a.originalapplicationid from dbo.Final_Business_App)
		
group by a.originalapplicationid, 
		 a.srv, 
		 b.applicationid 

having	(max(b.applicantid)) is not null
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Last_App_Found
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<M>--
if object_id('dbo.Applicant_Fact') is not null 
begin drop table dbo.Applicant_Fact end
--</M>--
select	
--<S>--
		originalapplicationid as originalapplicationid,
		srv as srv,
		applicantid as applicantid,
		applicationid as applicationid
--</S>--
into  
--<I>--
	dbo.Applicant_Fact
--</I>--
from	
--<F>--
		dbo.Final_Main_App
		union
		select * from dbo.Final_Business_App
		union
		select * from dbo.Last_App_Found

--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.Applicant_Fact
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_Applicant_Fact') is not null 
begin drop table dbo.fc_Master_Lookup_Applicant_Fact end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.AllowIsMarketingCustomerLists as AllowIsMarketingCustomerLists,
		b.AllowMarketingSMS as AllowMarketingSMS,
		b.AllowMarketingTeleCampaings as AllowMarketingTeleCampaings,
		b.ApplicantId as ApplicantId,
		b.ApplicantRepositoryId as ApplicantRepositoryId,
		b.ApplicantTypeId as ApplicantTypeId,
		b.ApplicationId as ApplicationId,
		b.ApplicationRelationId as ApplicationRelationId,
		b.Brand as Brand,
		b.Cell as Cell,
		b.CIS as CIS,
		b.CISStatus as CISStatus,
		b.City as City,
		b.ClientOccupationId as ClientOccupationId ,
		b.CompanyName as CompanyName,
		b.CorrespondenceLanguageId as CorrespondenceLanguageId,
		b.CurrentAccount as CurrentAccount,
		b.DateOfBirth as DateOfBirth,
		b.Dcar as Dcar,
		b.Email as Email,
		b.EmployeeNumber as EmployeeNumber,
		b.EthnicGroupId as EthnicGroupId,
		b.Fax as Fax,
		b.GenderId as GenderId,
		b.HasCA as HasCA,
		b.HasCIS as HasCIS,
		b.HasSA as HasSA,
		b.HomeBranch as HomeBranch,
		b.HomeTelephone as HomeTelephone,
		b.IdNo as idNo,
		b.IncomeCategoryId as IncomeCategoryId ,
		b.Initials as Initials,
		b.IsPostalSame as IsPostalSame,
		b.IsRSACitizen as IsRSACitizen,
		b.LanguageId as LanguageId,
		b.MaritalStatusId as MaritalStatusId ,
		b.MarketSector as MarketSector,
		b.MarketSegment as MarketSegment,
		b.[name] as [name],
		b.Nationality as Nationality,
		b.Postal1 as Postal1,
		b.Postal2 as Postal2,
		b.PostalCity as PostalCity,
		b.PostalCode as PostalCode,
		b.PostalPostalCode as PostalPostalCode,
		b.PostalSuburb as PostalSuburb,
		b.PreferredContactId as PreferredContactId,
		b.ProvinceId as ProvinceId,
		b.ReceivesVehicleAllowance as ReceivesVehicleAllowance,
		b.ResidenceTypeId as ResidenceTypeId,
		b.Residential1 as Residential1,
		b.Residential2 as Residential2,
		b.SA as SA,
		b.Suburb as Suburb,
		b.SuretyApplicantId as SuretyApplicantId,
		b.Surname as Surname,
		b.TitleId as TitleId,
		b.WorkTelephone as WorkTelephone,
		b.srv as srv 
--</S>--
into
--<I>--	
		dbo.fc_Master_Lookup_Applicant_Fact
--</I>--
from	
--<F>--
		dbo.Applicant_Fact a 
		left outer join 
		dbo.Union_m_applicant b 

on		a.applicantid = b.applicantid and 
		a.applicationid = b.ApplicationId and 
		a.srv = b.srv 

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and 
		1=2
--</F>--
--</SBLK>--
insert into
--<II>--
		dbo.fc_Master_Lookup_Applicant_Fact
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		b.AllowIsMarketingCustomerLists as AllowIsMarketingCustomerLists,
		b.AllowMarketingSMS as AllowMarketingSMS,
		b.AllowMarketingTeleCampaings as AllowMarketingTeleCampaings,
		b.ApplicantId as ApplicantId,
		b.ApplicantRepositoryId as ApplicantRepositoryId,
		b.ApplicantTypeId as ApplicantTypeId,
		b.ApplicationId as ApplicationId,
		b.ApplicationRelationId as ApplicationRelationId,
		b.Brand as Brand,
		b.Cell as Cell,
		b.CIS as CIS,
		b.CISStatus as CISStatus,
		b.City as City,
		b.ClientOccupationId as ClientOccupationId ,
		b.CompanyName as CompanyName,
		b.CorrespondenceLanguageId as CorrespondenceLanguageId,
		b.CurrentAccount as CurrentAccount,
		b.DateOfBirth as DateOfBirth,
		b.Dcar as Dcar,
		b.Email as Email,
		b.EmployeeNumber as EmployeeNumber,
		b.EthnicGroupId as EthnicGroupId,
		b.Fax as Fax,
		b.GenderId as GenderId,
		b.HasCA as HasCA,
		b.HasCIS as HasCIS,
		b.HasSA as HasSA,
		b.HomeBranch as HomeBranch,
		b.HomeTelephone as HomeTelephone,
		b.IdNo as idNo,
		b.IncomeCategoryId as IncomeCategoryId ,
		b.Initials as Initials,
		b.IsPostalSame as IsPostalSame,
		b.IsRSACitizen as IsRSACitizen,
		b.LanguageId as LanguageId,
		b.MaritalStatusId as MaritalStatusId ,
		b.MarketSector as MarketSector,
		b.MarketSegment as MarketSegment,
		b.[name] as [name],
		b.Nationality as Nationality,
		b.Postal1 as Postal1,
		b.Postal2 as Postal2,
		b.PostalCity as PostalCity,
		b.PostalCode as PostalCode,
		b.PostalPostalCode as PostalPostalCode,
		b.PostalSuburb as PostalSuburb,
		b.PreferredContactId as PreferredContactId,
		b.ProvinceId as ProvinceId,
		b.ReceivesVehicleAllowance as ReceivesVehicleAllowance,
		b.ResidenceTypeId as ResidenceTypeId,
		b.Residential1 as Residential1,
		b.Residential2 as Residential2,
		b.SA as SA,
		b.Suburb as Suburb,
		b.SuretyApplicantId as SuretyApplicantId,
		b.Surname as Surname,
		b.TitleId as TitleId,
		b.WorkTelephone as WorkTelephone,
		b.srv as srv 
--</S>--
from	
--<F>--
		dbo.Applicant_Fact a 
		left outer join 
		dbo.Union_m_applicant b 

on		a.applicantid = b.applicantid and 
		a.applicationid = b.ApplicationId and 
		a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0  
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_Applicant_Fact
([applicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------
--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.fc_Master_Lookup_IncomeCalculator') is not null 
begin drop table dbo.fc_Master_Lookup_IncomeCalculator end
--</M>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as serva, 
		b.AdditionalIncomeDisability as AdditionalIncomeDisability,
		b.AdditionalIncomeOther as AdditionalIncomeOther,
		b.AdditionalIncomePension as AdditionalIncomePension,
		b.Alimony as Alimony,
		b.ApplicantId as ApplicantId,
		b.ApplicationId as ApplicationId,
		b.BasicGross as BasicGross,
		b.Bonus as Bonus,
		b.Comments as Comments,
		b.CommissionMonth1 as CommissionMonth1 ,
		b.CommissionMonth2 as CommissionMonth2,
		b.CommissionMonth3 as CommissionMonth3,
		b.GrossPropertyRentalIncome as GrossPropertyRentalIncome,
		b.GroupInsurance as GroupInsurance ,
		b.Housing as Housing,
		b.IndustryCouncil as IndustryCouncil ,
		b.LeavePay as LeavePay,
		b.Medical as Medical,
		b.MedicalAid as MedicalAid,
		b.Overtime as Overtime,
		b.PAYE as PAYE,
		b.Pension as Pension,
		b.ReimbursementsOther as ReimbursementsOther,
		b.ReimbursementsPetrol as ReimbursementsPetrol ,
		b.ReimbursementsTelephone as ReimbursementsTelephone,
		b.SickPay as SickPay,
		b.Total as Total,
		b.Travel as Travel,
		b.UIF as UIF,
		b.UnionFees as UnionFees,
		b.srv as srv,
		b.BondLoan as BondLoan,
		b.CompanyLoan as CompanyLoan,
		b.InsurancePolicies as InsurancePolicies,
		b.OtherOther as OtherOther ,
		b.OtherTelephone as OtherTelephone,
		b.SavingsProvision as SavingsProvision,
		b.VehicleLoan as VehicleLoan,
		b.WaterLights as WaterLights

--</S>--
into	
--<I>--
		dbo.fc_Master_Lookup_IncomeCalculator
--</I>--

from	
--<F>--
		dbo.fc_Master_Lookup_Applicant_Fact a 
		left outer join
		dbo.Union_m_IncomeCalculator b

on		a.applicantid = b.applicantid and
		a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 and
		1=2
--</F>--
--</SBLK>--
insert into
--<II>--
		dbo.fc_Master_Lookup_IncomeCalculator
--</II>--
select	
--<S>--
		a.originalapplicationid as originalapplicationid, 
		a.srv as serva, 
		b.AdditionalIncomeDisability as AdditionalIncomeDisability,
		b.AdditionalIncomeOther as AdditionalIncomeOther,
		b.AdditionalIncomePension as AdditionalIncomePension,
		b.Alimony as Alimony,
		b.ApplicantId as ApplicantId,
		b.ApplicationId as ApplicationId,
		b.BasicGross as BasicGross,
		b.Bonus as Bonus,
		b.Comments as Comments,
		b.CommissionMonth1 as CommissionMonth1 ,
		b.CommissionMonth2 as CommissionMonth2,
		b.CommissionMonth3 as CommissionMonth3,
		b.GrossPropertyRentalIncome as GrossPropertyRentalIncome,
		b.GroupInsurance as GroupInsurance ,
		b.Housing as Housing,
		b.IndustryCouncil as IndustryCouncil ,
		b.LeavePay as LeavePay,
		b.Medical as Medical,
		b.MedicalAid as MedicalAid,
		b.Overtime as Overtime,
		b.PAYE as PAYE,
		b.Pension as Pension,
		b.ReimbursementsOther as ReimbursementsOther,
		b.ReimbursementsPetrol as ReimbursementsPetrol ,
		b.ReimbursementsTelephone as ReimbursementsTelephone,
		b.SickPay as SickPay,
		b.Total as Total,
		b.Travel as Travel,
		b.UIF as UIF,
		b.UnionFees as UnionFees,
		b.srv as srv,
		b.BondLoan as BondLoan,
		b.CompanyLoan as CompanyLoan,
		b.InsurancePolicies as InsurancePolicies,
		b.OtherOther as OtherOther ,
		b.OtherTelephone as OtherTelephone,
		b.SavingsProvision as SavingsProvision,
		b.VehicleLoan as VehicleLoan,
		b.WaterLights as WaterLights

--</S>--

from	
--<F>--
		dbo.fc_Master_Lookup_Applicant_Fact a 
		left outer join
		dbo.Union_m_IncomeCalculator b

on		a.applicantid = b.applicantid and
		a.srv = b.srv

where	b.applicationid is not null and 
		a.originalapplicationid is not null and 
		a.originalapplicationid <> 0 
--</F>--
CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appsrv] ON dbo.fc_Master_Lookup_IncomeCalculator
([originalapplicationid],[srv] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--
----------------------------------------------------------------------------------------------------------------------
----STRT_CMT10_(NOTE: Drop all tables that is not Master tables)----END_CMT10
--<M>--
drop table dbo.ApplicationState
drop table dbo.Initiation_Source
drop table dbo.TakenUp_Max	
drop table dbo.TakenUpTOB
drop table dbo.TermsOfBusiness
drop table dbo.Initial_TOB
drop table dbo.Final_TOB
drop table dbo.Final_TakenUp_TOB
drop table dbo.NVFLoanDetails
drop table dbo.NVFAsset
drop table dbo.AppStructure
drop table dbo.CA_LoanDetails
drop table dbo.PaymentMedium
drop table dbo.RateChange
drop table dbo.CashFlow
drop table dbo.Freeze
drop table dbo.AssetsLiabilities
drop table dbo.TermLoan
drop table dbo.DocumentationFeeChange
drop table dbo.CreditTurnover
drop table dbo.AccountDetails
drop table dbo.DecisionUpdated
drop table dbo.Initial_Decision
drop table dbo.Final_Decision
drop table dbo.Final_DecisionUpdate
drop table dbo.Updated_Union_Application_1
drop table dbo.First_Capturer
drop table dbo.Last_Capturer
drop table dbo.Capturer
drop table dbo.union_Application_2
drop table dbo.union_Application_3a
drop table dbo.union_Application_3
drop table dbo.union_Application_4
drop table dbo.union_Application_5
drop table dbo.Union_AppealRelook_1
drop table dbo.Union_AppealRelook_2
drop table dbo.Union_AppealRelook_3
drop table dbo.Union_ApplicationState_1
drop table dbo.Union_ApplicationState_2
drop table dbo.first_applicationstateid
drop table dbo.last_applicationstateid
drop table dbo.applicationstateid_s
drop table dbo.fc_Lookup_ApplicationState_Fact
drop table dbo.Final_Main_App
drop table dbo.Final_Business_App
drop table dbo.Last_App_Found
drop table dbo.Applicant_Fact
--</M>--
----------------------------------------------------------------------------------------------------------------

--<BLK>--
--<SBLK>--
--<M>--
if object_id('dbo.FACT_CAN') is not null 
begin drop table dbo.FACT_CAN end
--</M>--
select	
--<S>--
		a.Originalapplicationid as Originalapplicationid , 
		a.srv as srv, 
		a.first_capturer as First_Capturer, 
		a.last_capturer as Last_Capturer, 
		a.TakenUp as TakenUp , 
		a.requestid_prior as requestid_prior , 
		a.requestid_last as requestid_last , 
		a.ProductId_prior as ProductId_prior , 
		a.ProductId_last as ProductId_last , 
		a.Min_DateCaptured as Min_DateCaptured ,
		a.Max_DateCaptured as Max_DateCaptured, 
		substring(convert(char(10),
		a.Min_DateCaptured,112),1,6) as min_cycledte,
		substring(convert(char(10),
		a.Max_DateCaptured,112),1,6) as max_cycledte, 
		a.isVehicle as isVehicle , 
		a.IsTaxiDeal as IsTaxiDeal ,
		a.IsSBSApplication as IsSBSApplication , 
		a.IsPreApproval as IsPreApproval , 
		a.IsFrozen as IsFrozen , 
		a.HasBureauConsent as HasBureauConsent , 
		a.DaysCapturing as DaysCapturing , 
		a.assettypeid_prior as assettypeid_prior , 
		a.assettypeid_last as assettypeid_last , 
		a.ApplicationChannelId as ApplicationChannelId , 
		dbo.fn_fc_App_Applicant_Fact(r.originalapplicationid) as f_lu_m_applicant_fact ,
		r.WorkTelephone as WorkTelephone , 
		r.TitleId as TitleId , 
		r.Surname as Surname , 
		r.SuretyApplicantId as SuretyApplicantId , 
		r.Suburb as Suburb , 
		r.SA as SA , 
		r.Residential2 as Residential2 , 
		r.Residential1 as Residential1 , 
		r.ResidenceTypeId as ResidenceTypeId , 
		r.ReceivesVehicleAllowance as ReceivesVehicleAllowance , 
		r.ProvinceId as ProvinceId , 
		r.PreferredContactId as PreferredContactId , 
		r.PostalSuburb as PostalSuburb , 
		r.PostalPostalCode as PostalPostalCode , 
		r.PostalCode as PostalCode , 
		r.PostalCity as PostalCity , 
		r.Postal2 as Postal2 ,
		r.Postal1 as Postal1 , 
		r.Nationality as Nationality , 
		r.Name as Name , 
		r.MarketSegment as MarketSegment_Applicant , 
		r.MarketSector as MarketSector_Applicant , 
		r.MaritalStatusId as MaritalStatusId , 
		r.LanguageId as LanguageId , 
		r.IsRSACitizen as IsRSACitizen ,
		r.IsPostalSame as IsPostalSame ,
	    r.Initials as Initials ,
		r.IncomeCategoryId as IncomeCategoryId , 
		r.IdNo as IdNo , 
		r.HomeTelephone as HomeTelephone , 
		r.HomeBranch as HomeBranch , 
		r.HasSA as HasSA ,
		r.HasCIS as HasCIS , 
		r.HasCA as HasCA , 
		r.GenderId as GenderId , 
		r.Fax as Fax_Applicant , 
		r.EmployeeNumber as EmployeeNumber , 
		r.Email as Email_Applicant , 
		r.Dcar as Dcar_Applicant , 
		r.DateOfBirth as DateOfBirth ,
		r.CurrentAccount as CurrentAccount , 
		r.CompanyName as CompanyName , 
		r.ClientOccupationId as ClientOccupationId , 
		r.City as City , r.CIS as CIS_Applicant , 
		r.Cell as Cell , r.Brand as Brand , 
		r.ApplicationRelationId as ApplicationRelationId , 
		r.ApplicationId as ApplicationId_f_lu_m_applicant_fact , 
		r.ApplicantTypeId as ApplicantTypeId , 
		r.ApplicantId as ApplicantId_fact , 
		dbo.fn_fc_IncomeCalculator(t.originalapplicationid) as f_lu_m_IncomeCalculator ,
		t.ApplicantId as ApplicantId_Icalc , 
		t.ApplicationId as ApplicationId_Icalc , 
		t.BasicGross as BasicGross , 
		t.Travel as Travel , 
		t.Housing as Housing , 
		t.MedicalAid as MedicalAid , 
		t.ReimbursementsPetrol as ReimbursementsPetrol , 
		t.ReimbursementsTelephone as ReimbursementsTelephone , 
		t.ReimbursementsOther as ReimbursementsOther , 
		t.CommissionMonth1 as CommissionMonth1 , 
		t.CommissionMonth2 as CommissionMonth2 ,
		t.CommissionMonth3 as CommissionMonth3 , 
		t.Overtime as Overtime , 
		t.SickPay as SickPay ,
		t.LeavePay as LeavePay , 
		t.Bonus as Bonus , 
		t.PAYE as PAYE ,
		t.UIF as UIF , 
		t.Medical as Medical , 
		t.Pension as Pension , 
		t.GroupInsurance as GroupInsurance , 
		t.IndustryCouncil as IndustryCouncil , 
		t.UnionFees as UnionFees , 
		0 as BondLoan , 
		0 as VehicleLoan , 
		0 as InsurancePolicies , 
		0 as WaterLights ,
		0 as SavingsProvision , 
		0 as CompanyLoan , 
		0 as OtherTelephone , 
		0 as OtherOther , 
		t.Alimony as Alimony , 
		t.AdditionalIncomePension as AdditionalIncomePension , 
		t.AdditionalIncomeDisability as AdditionalIncomeDisability , 
		t.AdditionalIncomeOther as AdditionalIncomeOther , 
		t.GrossPropertyRentalIncome as GrossPropertyRentalIncome , 
		t.Comments as Comments , 
		t.Total as Total , 
		dbo.fn_fc_AppStructure(k.originalapplicationid) as f_lu_m_ApplicationStructure ,
		k.SuretyCount as SuretyCount , 
		k.SecurityCount as SecurityCount , 
		k.CoApplicantCount as CoApplicantCount , 
		k.ApplicationId as ApplicationId_f_lu_m_ApplicationStructure , 
		dbo.fn_fc_Initiation_Source(o.originalapplicationid) as f_lu_m_Initiation ,
		o.[user] as User_Initiation , 
		o.Phone as Phone , 
		o.OriginatorName as OriginatorName , 
		o.OriginationSourceId as OriginationSourceId , 
		o.InitiationSource as InitiationSource , 
		o.FullName as FullName , 
		o.Fax as Fax_Initiation , 
		o.Email as Email_Initiation , 
		o.DcarPrefix as DcarPrefix , 
		o.Dcar as Dcar_Initiation , 
		o.BrandCode as BrandCode , 
		o.BranchCode as BranchCode , 
		o.ApplicationSource as ApplicationSource , 
		o.ApplicationId as ApplicationId_f_lu_m_Initiation , 
		dbo.fn_fc_Lookup_TOB(n.originalapplicationid)as f_lu_m_TermsOfBusiness ,
		n.Applicationid as ApplicationID_TOB, 
		n.Term as Term , 
		n.ScoreScaled as ScoreScaled , 
		n.ScoreNGR as ScoreNGR , 
		n.RateThreshold as RateThreshold , 
		n.Rate as Rate_TOB , 
		n.NVFPurchase as NVFPurchase , 
		n.NVFNetLoan as NVFNetLoan , 
		n.NVFDocumentationFeePercentage as NVFDocumentationFeePercentage , 
		n.NVFDocumentationFee as NVFDocumentationFee , 
		n.NVFBalloon as NVFBalloon , 
		n.IsAppealAllowed as IsAppealAllowed , 
		n.Installment as Installment_TOB , 
		n.HLValuation as HLValuation , 
		n.HLLoanToValue as HLLoanToValue , 
		n.HLLoan as HLLoan , 
		n.HLCalculated as HLCalculated , 
		n.FraudReferral as FraudReferral , 
		n.FLTotal as FLTotal , 
		n.ExpiryDate as ExpiryDate_TOB , 
		n.Deposit as Deposit_TermsOfBusiness , 
		n.DecisionCode as DecisionCode , 
		n.CATotalLimit as CATotalLimit ,
		n.CAAdditionalLimit as CAAdditionalLimit , 
		n.BureauScore as BureauScore , 
		n.BureauNotices as BureauNotices , 
		n.BureauJudgements as BureauJudgements , 
		n.BureauIdVerify as BureauIdVerify ,
		n.BureauEnquiryDate as BureauEnquiryDate , 
		n.BureauAdverses as BureauAdverses , 
		n.Bureau as Bureau , 
		n.InitialTOBRate as InitialTOBRate, 
		n.InitialTOBDecision as InitialTOBDecision, 
		n.InitialTOBTerm as InitialTOBTerm, 
		n.InitialTOBNVFNetLoan as InitialTOBNVFNetLoan, 
		n.InitialTOBCaTotalLimit as InitialTOBCaTotalLimit,
		n.InitialTOBScoreNGR as InitialTOBScoreNGR, 
		n.InitialTOBScoreScaled as InitialTOBScoreScaled, 
		n.InitialTOBBureauScore as InitialTOBBureauScore,
		n.FinalTOBRate as FinalTOBRate, 
		n.FinalTOBDecision as FinalTOBDecision, 
		n.FinalTOBTerm as FinalTOBTerm, 
		n.FinalTOBNVFNetLoan as FinalTOBNVFNetLoan, 
		n.FinalTOBCaTotalLimit as FinalTOBCaTotalLimit,
		n.FinalTOBScoreNGR as FinalTOBScoreNGR, 
		n.FinalTOBScoreScaled as FinalTOBScoreScaled,
		n.FinalTOBBureauScore as FinalTOBBureauScore, 
		n.TakenUpTOBRate as TakenUpTOBRate, 
		n.TakenUpTOBDecision as TakenUpTOBDecision, 
		n.TakenUpTOBTerm as TakenUpTOBTerm, 
		n.TakenUpTOBNVFNetLoan as TakenUpTOBNVFNetLoan, 
		n.TakenUpTOBCaTotalLimit as TakenUpTOBCaTotalLimit, 
		n.TakenUpTOBScoreNGR as TakenUpTOBScoreNGR, 
		n.TakenUpTOBScoreScaled as TakenUpTOBScoreScaled, 
		n.ApplicationId as ApplicationId_f_lu_m_TermsOfBusiness  , 
		dbo.fn_fc_AppealReLook_Fact(q.originalapplicationid) as f_lu_m_appealrelook_fact ,
		q.Relook_sum as Relook_sum , 
		q.max_AppealCount as max_AppealCount , 
		q.ChangeSupportingDocumetsdetails_sum as ChangeSupportingDocumetsdetails_sum , 
		q.ChangeSecurityinformation_sum as ChangeSecurityinformation_sum , 
		q.ChangePaymentMediumDetails_sum as ChangePaymentMediumDetails_sum , 
		q.ChangeLoaninformation_sum as ChangeLoaninformation_sum , 
		q.ChangeDealerinformation_sum as ChangeDealerinformation_sum , 
		q.ChangeAssetinformation_sum as ChangeAssetinformation_sum ,
		q.ChangeApplicationinformation_sum as ChangeApplicationinformation_sum , 
		q.AReasonX as AReasonX , 
		q.AReason5 as AReason5 , 
		q.AReason4 as AReason4 , 
		q.AReason3 as AReason3 , 
		q.AReason2 as AReason2 , 
		q.AReason1 as AReason1 ,
		q.Appealrelookstaffware_sum as Appealrelookstaffware_sum , 
		dbo.fn_fc_RateChange(h.originalapplicationid) as f_lu_m_RateChange ,
		h.[user] as User_RateChg ,
		h.Updated as Updated_RateChg ,
		h.RateAdjustmentId as RateAdjustmentId , 
		h.newInterestRate as newInterestRate , 
		h.Decision as Decision , 
		h.DateCaptured as DateCaptured , 
		h.Comments as Comments_RateChange , 
		h.approvedInterestRate as approvedInterestRate , 
		h.ApplicationId as ApplicationId_f_lu_m_RateChange , 
		dbo.fn_fc_DocumentationFeeChange(c.originalapplicationid) as f_lu_m_DocumentationFeeChange ,
		c.[user] as User_DocFeeChg , 
		c.Updated as Updated_DocFeeChg , 
		c.OptionDecision as OptionDecision , 
		c.NewDocumentationFee as NewDocumentationFee , 
		c.DocumenationFeeOptionId as DocumentationFeeOptionId , 
		c.DocumentationFeeDecision as DocumentationFeeDecision , 
		c.DocumentationFeeAdjustmentId as DocumentationFeeAdjustmentId , 
		c.Date as Date_DocfeeChrg , 
		c.Comments as Comments_DocFeeChrg , 
		c.ApprovedDocumentationFee as ApprovedDocumentationFee , 
		c.ApplicationId as ApplicationId_f_lu_m_DocumentationFeeChange , 
		dbo.fn_fc_PaymentMedium(i.originalapplicationid) as f_lu_m_PaymentMedium  ,
		i.OtherAccountNumber as OtherAccountNumber , 
		i.IsNedbankCA as IsNedbankCA , 
		i.IsCurrentAccount as IsCurrentAccount , 
		i.ClearingCode as ClearingCode , 
		i.BranchName as BranchName , i.Bank as Bank , 
		i.ApplicationId as ApplicationId_f_lu_m_PaymentMedium , 
		i.AccountTypeId as AccountTypeId , 
		i.AccountNumber as AccountNumber , 
		dbo.fn_fc_AppFreeze(f.originalapplicationid) as f_lu_m_Freeze ,
		f.[user] as User_Freeze , 
		f.Updated as Updated_Freeze , 
		f.FreezeReason5 as FreezeReason5 , 
		f.FreezeReason4 as FreezeReason4 , 
		f.FreezeReason3 as FreezeReason3 , 
		f.FreezeReason2 as FreezeReason2 , 
		f.FreezeReason1 as FreezeReason1 ,
		f.FreezeId as FreezeId , 
		f.[Date] as Date_Freeze , 
		f.Comments as Comments_Freeze , 
		f.ApplicationId as ApplicationId_f_lu_m_Freeze , 
		dbo.fn_fc_CashFlow (g.originalapplicationid) as f_lu_m_CashFlow  ,
		g.TotalDebtors as TotalDebtors_CashFlow , 
		g.TotalCurrentLiabilities as TotalCurrentLiabilities_CashFlow , 
		g.TotalCurrentAssets as TotalCurrentAssets_CashFlow , 
		g.TotalCreditors as TotalCreditors_CashFlow , 
		g.PositiveOpeningBankBalance as PositiveOpeningBankBalance , 
		g.OpningStock as OpeningStock_CashFlow , 
		g.NetProfitMonth9 as NetProfitMonth9 , 
		g.NetProfitMonth8 as NetProfitMonth8 , 
		g.NetProfitMonth7 as NetProfitMonth7 , 
		g.NetProfitMonth6 as NetProfitMonth6 ,	
		g.NetProfitMonth5 as NetProfitMonth5 , 
		g.NetProfitMonth4 as NetProfitMonth4 , 
		g.NetProfitMonth3 as NetProfitMonth3 , 
		g.NetProfitMonth2 as NetProfitMonth2 , 
		g.NetProfitMonth12 as NetProfitMonth12 , 
		g.NetProfitMonth11 as NetProfitMonth11 , 
		g.NetProfitMonth10 as NetProfitMonth10 , 
		g.NetProfitMonth1 as NetProfitMonth1 , 
		g.NetProfitActual as NetProfitActual , 
		g.NegativeOpeningBankBalance as NegativeOpeningBankBalance , 
		g.DisposableCashMonth9 as DisposableCashMonth9 , 
		g.DisposableCashMonth8 as DisposableCashMonth8 , 
		g.DisposableCashMonth7 as DisposableCashMonth7 , 
		g.DisposableCashMonth6 as DisposableCashMonth6 , 
		g.DisposableCashMonth5 as DisposableCashMonth5 , 
		g.DisposableCashMonth4 as DisposableCashMonth4 , 
		g.DisposableCashMonth3 as DisposableCashMonth3 , 
		g.DisposableCashMonth2 as DisposableCashMonth2 , 
		g.DisposableCashMonth12 as DisposableCashMonth12 , 
		g.DisposableCashMonth11 as DisposableCashMonth11 , 
		g.DisposableCashMonth10 as DisposableCashMonth10 , 
		g.DisposableCashMonth1 as DisposableCashMonth1 , 
		g.DisposableCashActual as DisposableCashActual , 
		g.CashFlowTurnoverActual as CashFlowTurnoverActual , 
		g.CashFlowMonth9 as CashFlowMonth9 , 
		g.CashFlowMonth8 as CashFlowMonth8 , 
		g.CashFlowMonth7 as CashFlowMonth7 , 
		g.CashFlowMonth6 as CashFlowMonth6 , 
		g.CashFlowMonth5 as CashFlowMonth5 , 
		g.CashFlowMonth4 as CashFlowMonth4 , 
		g.CashFlowMonth3 as CashFlowMonth3 , 
		g.CashFlowMonth2 as CashFlowMonth2 , 
		g.CashFlowMonth12 as CashFlowMonth12 , 
		g.CashFlowMonth11 as CashFlowMonth11 , 
		g.CashFlowMonth10 as CashFlowMonth10 , 
		g.CashFlowMonth1 as CashFlowMonth1 , 
		g.AverageCreditTurnover as AverageCreditTurnover , 
		g.ApplicationId as ApplicationId_f_lu_m_CashFlow, 
		dbo.fn_fc_CreditTurnover(b.originalapplicationid) as f_lu_m_CreditTurnover,
		b.Turnover as Turnover , 
		b.Deposit5 as Deposit5 ,
		b.Deposit4 as Deposit4 , 
		b.Deposit3 as Deposit3 , 
		b.Deposit2 as Deposit2 , 
		b.Deposit1 as Deposit1 , 
		b.Deposit as Deposit_CreditTurnover , 
		b.Credits5 as Credits5 , 
		b.Credits4 as Credits4 , 
		b.Credits3 as Credits3 , 
		b.Credits2 as Credits2 , 
		b.Credits1 as Credits1 , 
		b.Credits as Credits , 
		b.ApplicationId as ApplicationId_f_lu_m_CreditTurnover , 
		dbo.fn_fc_AssetsLiabilities(e.originalapplicationid) as f_lu_m_AssetsLiabilities ,
		e.TotalDebtors as TotalDebtors_AssetsLiabilities , 
		e.TotalCurrentLiabilities as TotalCurrentLiabilities_AssetsLiabilities , 
		e.TotalCurrentAssets as TotalCurrentAssets_AssetsLiabilities , 
		e.TotalCreditors as TotalCreditors_AssetsLiabilities , 
		e.OpeningStock as OpeningStock_AssetsLiabilities , 
		e.BankBalanceLiability as BankBalanceLiability , 
		e.BankBalanceAsset as BankBalanceAsset , 
		e.ApplicationId as ApplicationId_f_lu_m_AssetsLiabilities , 
		dbo.fn_fc_CALoanDetails (j.originalapplicationid) as _lu_m_CALoanDetails ,
		j.TotalLimit as TotalLimit , 
		j.SourceOfRepayment as SourceOfRepayment_CALoanDet , 
		j.RequiredRepayment as RequiredRepayment , 
		j.RequiredRate as RequiredRate , 
		j.RequestDescription as RequestDescription , 
		j.RepaymentPeriod as RepaymentPeriod_CALoanDet , 
		j.ReductionTypeId as ReductionTypeId , 
		j.ReductionStartDate as ReductionStartDate_CALoanDetails , 
		j.ReductionAmount as ReductionAmount_CALoanDetails , 
		j.ReduceTo as ReduceTo , 
		j.PurposeOfFacility as PurposeOfFacility , 
		j.MustFacilityReduce as MustFacilityReduce , 
		j.Motivation as Motivation_CALoanDet , 
		j.GuaranteeAmount as GuaranteeAmount , 
		j.ExpiryDate as ExpiryDate_CALoanDet , 
		j.ExistingRepaymentAmount as ExistingRepaymentAmount , 
		j.ExistingRepayment as ExistingRepayment , 
		j.ExistingExpiryDate as ExistingExpiryDate , 
		j.CurrentLimit as CurrentLimit_CALoanDetails , 
		j.BeneficiaryDetails as BeneficiaryDetails , 
		j.ApplicationId as ApplicationId_f_lu_m_CALoanDetails  , 
		j.AdditionalLimit as AdditionalLimit_CALoanDetails , 
		dbo.fn_fc_NVFAsset(l.originalapplicationid) as  f_lu_m_NVFAsset ,
		l.[Year] as Year_ , 
		l.TradeValue4 as TradeValue4 , 
		l.TradeValue3 as TradeValue3 , 
		l.TradeValue2 as TradeValue2 , 
		l.TradeValue1 as TradeValue1 ,
		l.TradeValue as TradeValue , 
		l.SpecifyOther as SpecifyOther , 
		l.RetailValue4 as RetailValue4 , 
		l.RetailValue3 as RetailValue3 , 
		l.RetailValue2 as RetailValue2 , 
		l.RetailValue1 as RetailValue1 , 
		l.RetailValue as RetailValue , 
		l.Model as Model , 
		l.MMCode as MMCode , 
		l.Make as Make , 
		l.KM as KM , 
		l.IsNew as IsNew , 
		l.ExtrasValueOther as ExtrasValueOther , 
		l.ExtrasValue5 as ExtrasValue5 , 
		l.ExtrasValue4 as ExtrasValue4 , 
		l.ExtrasValue3 as ExtrasValue3 , 
		l.ExtrasValue2 as ExtrasValue2 , 
		l.ExtrasValue1 as ExtrasValue1 , 
		l.ExtrasDescriptionOther as ExtrasDescriptionOther , 
		l.ExtrasDescription5 as ExtrasDescription5 , 
		l.ExtrasDescription4 as ExtrasDescription4 , 
		l.ExtrasDescription3 as ExtrasDescription3 , 
		l.ExtrasDescription2 as ExtrasDescription2 , 
		l.ExtrasDescription1 as ExtrasDescription1 , 
		0 as ExpectedKM , 
		l.Condition as Condition , 
		l.AssetId as AssetId , 
		l.ApplicationId as ApplicationId_f_lu_m_NVFAsset , 
		dbo.fn_fc_TermLoan(d.originalapplicationid) as f_lu_m_TermLoan ,
		d.SourceOfRepayment as SourceOfRepayment_TermLoan , 
		d.RepaymentPeriod as RepaymentPeriod_TermLoan , 
		d.RateWithinPolicy as RateWithinPolicy , 
		d.RateTypeId as RateTypeId , 
		d.RateAuthorisation as RateAuthorisation , 
		d.Rate as Rate_TermLoan , 
		d.PaymentFrequencyId as PaymentFrequencyId , 
		d.Motivation as Motivation_TermLoan , 
		d.LoanAmount as LoanAmount , 
		d.FirstPaymentDate as FirstPaymentDate ,
		d.FinanceTypeId as FinanceTypeId , 
		0 as DocumentationFeeId , 
		d.ArrearsAdvanceId as ArrearsAdvanceId , 
		d.ApplicationId as ApplicationId_f_lu_m_TermLoan ,
		dbo.fn_fc_ApplicationState_Fact(s.originalapplicationid) as f_lu_m_ApplicationState_fact  ,
		s.Asses_sub as Asses_sub , 
		s.ApplicationId as  ApplicationId_f_lu_m_ApplicationState_fact , 
		s.StaffwareCase as StaffwareCase , 
		s.First_ApplicationStateId as First_ApplicationStateId , 
		s.Last_ApplicationStateId as Last_ApplicationStateId , 
		s.AssessmentComments as AssessmentComments , 
		s.StaffwareStateId as StaffwareStateId , 
		s.TransactStateId as TransactStateId , 
		s.StaffwareSubmitDate as StaffwareSubmitDate , 
		s.TransactSubmitDate as TransactSubmitDate , 
		s.ApplicationDecisionId as ApplicationDecisionId , 
		s.PreAppealStateId as PreAppealStateId , 
		0 as f_lu_m_ContractualDebt_fact ,
		0 as ApplicantId , 
		0 as NVFInstallment , 
		0 as HLInstallment ,
		0 as NVFSettlement , 
		0 as HLSettlement , 
		0 as OverdraftOther , 
		0 as CreditCardOther , 
		0 as OverdraftSettlement , 
		t.Total as NetMonthlyIncome , 
		0 as TotalExpenses , 
		0 as TotalLoanPayments , 
		dbo.fn_fc_AccountDetails_fact(v.originalapplicationid)as f_lu_m_AccountDetails_fact ,
		v.ApplicationId as ApplicationId_AccountDetails , 
		v.CIS as CIS_AccountDetails , 
		v.BrandId as BrandId , 
		v.Dcar as Dcar , 
		v.MarketSegment as MarketSegment_AccountDetails , 
		v.MarketSector as MarketSector_AccountDetails , 
		v.CANumber as CANumber , 
		v.NVFNumber as NVFNumber , 
		v.HLNumber as HLNumber , 
		v.SANumber as SANumber , 
		v.HasPersonalLoan as HasPersonalLoan , 
		v.PersonalLoanNumber as PersonalLoanNumber , 
		v.IsBusinessApplication as IsBusinessApplicaition , 
		v.IsPrincipleApplication as IsPrincipleApplication , 
		dbo.fn_fc_DecisionUpdate_fact(w.originalapplicationid) as f_lu_m_DecisionUpdate_fact ,
		w.Applicationid as Applicationid_Final_DecisionUpdate , 
		w.DecisionUpdateId as DecisionUpdateId , 
		w.ApplicationId as ApplicationId , 
		w.FinalVerificationOutcomeId as FinalVerificationOutcomeId , 
		w.FinalAppealDecisionId as FinalAppealDecisionId , 
		w.AppealAllowed as AppealAllowed , 
		w.AppealAssessmentComments as AppealAssessmentComments , 
		w.OverrideReasonId as OverrideReasonId , 
		w.DecisionUser as DecisionUser , 
		w.DecisionDate as DecisionDate , 
		w.AppealCounter as AppealCounter , 
		w.TotalApprovedLimit as TotalApprovedLimit , 
		w.PurchasePrice as PurchasePrice , 
		w.Installment as Installment_Final_DecisionUpdate , 
		w.Rate as Rate_Final_DecisionUpdate , 
		w.Term as Terlu_m_Final_DecisionUpdate , 
		w.DepositAmount as DepositAmount_Final_DecisionUpdate , 
		w.BalloonAmount as BalloonAmount_Final_DecisionUpdate , 
		w.LoanToValue as LoanToValue_Final_DecisionUpdate , 
		w.ChangeTOB as ChangeTOB , 
		w.CurrentLimit as CurrentLimit_Final_Final_DecisionUpdate , 
		w.AdditionalLimit as AdditionalLimit_Final_DecisionUpdate , 
		w.MonthlyReductionAmount as MonthlyReductionAmount , 
		w.OnceOffExpiryDate as OnceOffExpiryDate , 
		w.ReduceBackTo as ReduceBackTo , 
		w.ReductionStartDate as ReductionStartDate_Final_DecisionUpdate , 
		w.ReductionAmount as ReductionAmount_Final_DecisionUpdate , 0 as IsIncomeValidated ,
		w.DeclineReasonId as DeclineReasonId,
		w.InitialDecisionUser as InitialDecisionUser, 
		w.InitialAdditionalLimit as InitialAdditionalLimit,
		w.InitialFinalAppealDecisionid as InitialFinalAppealDecisionid, 
		w.InitialTotalApprovedlimit as InitialTotalApprovedlimit,
		w.InitialOverrideReasonId as InitialOverrideReasonId, 
		w.InitialRate as InitialRate, 
		w.finalDecisionUser as finalDecisionUser,
		w.FinalAdditionalLimit as FinalAdditionalLimit,	
		w.FinalFinalAppealDecisionid as FinalFinalAppealDecisionid,
		w.FinalTotalApprovedlimit as FinalTotalApprovedlimit,
		w.FinalOverrideReasonId as FinalOverrideReasonId, 
		w.FinalRate as FinalRate,
		dbo.fn_fc_NVFLoanDetails_fact(x.originalapplicationid) as  f_lu_m_NVFLoanDetails_fact  ,
		x.DepositAmount as NVF_Deposit_Amount, 
		x.PercentageDeposit as NVF_Deposit_Perc, 
		x.IsSchemeClient as IsSchemeClient,
		dbo.fn_fc_CanDecision(w.applicationid,n.applicationid,w.DecisionUpdateId,n.decisioncode) as CAN_DECISION,
		dbo.fn_fc_Can_Decision_Source(w.applicationid,n.applicationid) as Can_Decision_source,
		dbo.fn_fc_Rule_Finaldecision(w.finalappealdecisionid,n.decisioncode) as Rule_Finaldecision,
		dbo.fn_fc_Rule_FinalLimitOffered(w.totalapprovedlimit,n.CaTotalLimit) as Rule_FinalLimitOffered,
		dbo.fn_fc_Rule_FinalRate(h.newinterestrate,w.finalrate,n.FinalTobRate) as Rule_FinalRate,
		dbo.fn_fc_Rule_RateDifference(n.finaltobrate,w.finalrate) as Rule_RateDifference,
		dbo.fn_fc_Rule_Rule_LargeLimit(w.totalapprovedlimit,n.CaTotalLimit,j.mustfacilityReduce) as Rule_LargeLimit
--</S>--
into	
--<I>--
	dbo.FACT_CAN
--</I>--

from	
--<F>--
		dbo.fc_Master_Lookup_Application_Fact a 
		left outer join 
		dbo.fc_Master_Lookup_CreditTurnover b 

on		a.originalapplicationid = b.originalapplicationid and 
		a.srv = b.srv
		left outer join 
		dbo.fc_Master_Lookup_DocumentationFeeChange c 

on		a.originalapplicationid = c.originalapplicationid and 
		a.srv = c.srv
		left outer join 
		dbo.fc_Master_Lookup_TermLoan d 

on		a.originalapplicationid = d.originalapplicationid and
		a.srv = d.srv
		left outer join 
		dbo.fc_Master_Lookup_AssetsLiabilities e 

on		a.originalapplicationid = e.originalapplicationid and 
		a.srv = e.srv
		left outer join 
		dbo.fc_Master_Lookup_AppFreeze f 

on		a.originalapplicationid = f.originalapplicationid and 
		a.srv = f.srv
		left outer join 
		dbo.fc_Master_Lookup_CashFlow g 

on		a.originalapplicationid = g.originalapplicationid and 
		a.srv = g.srv
		left outer join 
		dbo.fc_Master_Lookup_RateChange h 

on		a.originalapplicationid = h.originalapplicationid and 
		a.srv = h.srv
		left outer join 
		dbo.fc_Master_Lookup_PaymentMedium i 

on		a.originalapplicationid = i.originalapplicationid and 
		a.srv = i.srv
		left outer join 
		dbo.fc_Master_Lookup_CALoanDetails j 

on		a.originalapplicationid = j.originalapplicationid and 
		a.srv = j.srv
		left outer join 
		dbo.fc_Master_Lookup_AppStructure k 

on		a.originalapplicationid = k.originalapplicationid and 
		a.srv = k.srv
		left outer join 
		dbo.fc_Master_Lookup_NVFAsset l 

on		a.originalapplicationid = l.originalapplicationid and 
		a.srv = l.srv
		left outer join 
		dbo.fc_Master_Lookup_TOB n 

on		a.originalapplicationid = n.originalapplicationid and 
		a.srv = n.srv
		left outer join 
		dbo.fc_Master_Lookup_Initiation_Source o 

on		a.originalapplicationid = o.originalapplicationid and 
		a.srv = o.srv
		left outer join 
		dbo.fc_Master_AppealReLook_Fact q 

on		a.originalapplicationid = q.originalapplicationid and 
		a.srv = q.srv
		left outer join 
		dbo.fc_Master_Lookup_Applicant_Fact r 

on		a.originalapplicationid = r.originalapplicationid and 
		a.srv = r.srv
		left outer join 
		dbo.fc_Master_Lookup_ApplicationState_Fact s 

on		a.originalapplicationid = s.originalapplicationid and 
		a.srv = s.srv
		left outer join dbo.fc_Master_Lookup_IncomeCalculator t 

on		a.originalapplicationid = t.originalapplicationid  and 
		a.srv = t.srv
		left outer join dbo.fc_Master_Lookup_AccountDetails v 

on		a.originalapplicationid = v.originalapplicationid and 
		a.srv = v.srv
		left outer join 
		dbo.fc_Master_Lookup_Final_DecisionUpdate w 

on		a.originalapplicationid = w.originalapplicationid and 
		a.srv = w.srv
		left outer join 
		dbo.fc_Master_Lookup_NVFLoanDetails x

on		a.originalapplicationid = x.originalapplicationid and 
		a.srv = x.srv

where	1=2
--</F>--
--</SBLK>--
insert into
--<II>--
	dbo.FACT_CAN
--</II>--
select	
--<S>--
		a.Originalapplicationid as Originalapplicationid , 
		a.srv as srv, 
		a.first_capturer as First_Capturer, 
		a.last_capturer as Last_Capturer, 
		a.TakenUp as TakenUp , 
		a.requestid_prior as requestid_prior , 
		a.requestid_last as requestid_last , 
		a.ProductId_prior as ProductId_prior , 
		a.ProductId_last as ProductId_last , 
		a.Min_DateCaptured as Min_DateCaptured ,
		a.Max_DateCaptured as Max_DateCaptured, 
		substring(convert(char(10),
		a.Min_DateCaptured,112),1,6) as min_cycledte,
		substring(convert(char(10),
		a.Max_DateCaptured,112),1,6) as max_cycledte, 
		a.isVehicle as isVehicle , 
		a.IsTaxiDeal as IsTaxiDeal ,
		a.IsSBSApplication as IsSBSApplication , 
		a.IsPreApproval as IsPreApproval , 
		a.IsFrozen as IsFrozen , 
		a.HasBureauConsent as HasBureauConsent , 
		a.DaysCapturing as DaysCapturing , 
		a.assettypeid_prior as assettypeid_prior , 
		a.assettypeid_last as assettypeid_last , 
		a.ApplicationChannelId as ApplicationChannelId , 
		dbo.fn_fc_App_Applicant_Fact(r.originalapplicationid) as f_lu_m_applicant_fact ,
		r.WorkTelephone as WorkTelephone , 
		r.TitleId as TitleId , 
		r.Surname as Surname , 
		r.SuretyApplicantId as SuretyApplicantId , 
		r.Suburb as Suburb , 
		r.SA as SA , 
		r.Residential2 as Residential2 , 
		r.Residential1 as Residential1 , 
		r.ResidenceTypeId as ResidenceTypeId , 
		r.ReceivesVehicleAllowance as ReceivesVehicleAllowance , 
		r.ProvinceId as ProvinceId , 
		r.PreferredContactId as PreferredContactId , 
		r.PostalSuburb as PostalSuburb , 
		r.PostalPostalCode as PostalPostalCode , 
		r.PostalCode as PostalCode , 
		r.PostalCity as PostalCity , 
		r.Postal2 as Postal2 ,
		r.Postal1 as Postal1 , 
		r.Nationality as Nationality , 
		r.Name as Name , 
		r.MarketSegment as MarketSegment_Applicant , 
		r.MarketSector as MarketSector_Applicant , 
		r.MaritalStatusId as MaritalStatusId , 
		r.LanguageId as LanguageId , 
		r.IsRSACitizen as IsRSACitizen ,
		r.IsPostalSame as IsPostalSame ,
	    r.Initials as Initials ,
		r.IncomeCategoryId as IncomeCategoryId , 
		r.IdNo as IdNo , 
		r.HomeTelephone as HomeTelephone , 
		r.HomeBranch as HomeBranch , 
		r.HasSA as HasSA ,
		r.HasCIS as HasCIS , 
		r.HasCA as HasCA , 
		r.GenderId as GenderId , 
		r.Fax as Fax_Applicant , 
		r.EmployeeNumber as EmployeeNumber , 
		r.Email as Email_Applicant , 
		r.Dcar as Dcar_Applicant , 
		r.DateOfBirth as DateOfBirth ,
		r.CurrentAccount as CurrentAccount , 
		r.CompanyName as CompanyName , 
		r.ClientOccupationId as ClientOccupationId , 
		r.City as City , r.CIS as CIS_Applicant , 
		r.Cell as Cell , r.Brand as Brand , 
		r.ApplicationRelationId as ApplicationRelationId , 
		r.ApplicationId as ApplicationId_f_lu_m_applicant_fact , 
		r.ApplicantTypeId as ApplicantTypeId , 
		r.ApplicantId as ApplicantId_fact , 
		dbo.fn_fc_IncomeCalculator(t.originalapplicationid) as f_lu_m_IncomeCalculator ,
		t.ApplicantId as ApplicantId_Icalc , 
		t.ApplicationId as ApplicationId_Icalc , 
		t.BasicGross as BasicGross , 
		t.Travel as Travel , 
		t.Housing as Housing , 
		t.MedicalAid as MedicalAid , 
		t.ReimbursementsPetrol as ReimbursementsPetrol , 
		t.ReimbursementsTelephone as ReimbursementsTelephone , 
		t.ReimbursementsOther as ReimbursementsOther , 
		t.CommissionMonth1 as CommissionMonth1 , 
		t.CommissionMonth2 as CommissionMonth2 ,
		t.CommissionMonth3 as CommissionMonth3 , 
		t.Overtime as Overtime , 
		t.SickPay as SickPay ,
		t.LeavePay as LeavePay , 
		t.Bonus as Bonus , 
		t.PAYE as PAYE ,
		t.UIF as UIF , 
		t.Medical as Medical , 
		t.Pension as Pension , 
		t.GroupInsurance as GroupInsurance , 
		t.IndustryCouncil as IndustryCouncil , 
		t.UnionFees as UnionFees , 
		0 as BondLoan , 
		0 as VehicleLoan , 
		0 as InsurancePolicies , 
		0 as WaterLights ,
		0 as SavingsProvision , 
		0 as CompanyLoan , 
		0 as OtherTelephone , 
		0 as OtherOther , 
		t.Alimony as Alimony , 
		t.AdditionalIncomePension as AdditionalIncomePension , 
		t.AdditionalIncomeDisability as AdditionalIncomeDisability , 
		t.AdditionalIncomeOther as AdditionalIncomeOther , 
		t.GrossPropertyRentalIncome as GrossPropertyRentalIncome , 
		t.Comments as Comments , 
		t.Total as Total , 
		dbo.fn_fc_AppStructure(k.originalapplicationid) as f_lu_m_ApplicationStructure ,
		k.SuretyCount as SuretyCount , 
		k.SecurityCount as SecurityCount , 
		k.CoApplicantCount as CoApplicantCount , 
		k.ApplicationId as ApplicationId_f_lu_m_ApplicationStructure , 
		dbo.fn_fc_Initiation_Source(o.originalapplicationid) as f_lu_m_Initiation ,
		o.[user] as User_Initiation , 
		o.Phone as Phone , 
		o.OriginatorName as OriginatorName , 
		o.OriginationSourceId as OriginationSourceId , 
		o.InitiationSource as InitiationSource , 
		o.FullName as FullName , 
		o.Fax as Fax_Initiation , 
		o.Email as Email_Initiation , 
		o.DcarPrefix as DcarPrefix , 
		o.Dcar as Dcar_Initiation , 
		o.BrandCode as BrandCode , 
		o.BranchCode as BranchCode , 
		o.ApplicationSource as ApplicationSource , 
		o.ApplicationId as ApplicationId_f_lu_m_Initiation , 
		dbo.fn_fc_Lookup_TOB(n.originalapplicationid)as f_lu_m_TermsOfBusiness ,
		n.Applicationid as ApplicationID_TOB, 
		n.Term as Term , 
		n.ScoreScaled as ScoreScaled , 
		n.ScoreNGR as ScoreNGR , 
		n.RateThreshold as RateThreshold , 
		n.Rate as Rate_TOB , 
		n.NVFPurchase as NVFPurchase , 
		n.NVFNetLoan as NVFNetLoan , 
		n.NVFDocumentationFeePercentage as NVFDocumentationFeePercentage , 
		n.NVFDocumentationFee as NVFDocumentationFee , 
		n.NVFBalloon as NVFBalloon , 
		n.IsAppealAllowed as IsAppealAllowed , 
		n.Installment as Installment_TOB , 
		n.HLValuation as HLValuation , 
		n.HLLoanToValue as HLLoanToValue , 
		n.HLLoan as HLLoan , 
		n.HLCalculated as HLCalculated , 
		n.FraudReferral as FraudReferral , 
		n.FLTotal as FLTotal , 
		n.ExpiryDate as ExpiryDate_TOB , 
		n.Deposit as Deposit_TermsOfBusiness , 
		n.DecisionCode as DecisionCode , 
		n.CATotalLimit as CATotalLimit ,
		n.CAAdditionalLimit as CAAdditionalLimit , 
		n.BureauScore as BureauScore , 
		n.BureauNotices as BureauNotices , 
		n.BureauJudgements as BureauJudgements , 
		n.BureauIdVerify as BureauIdVerify ,
		n.BureauEnquiryDate as BureauEnquiryDate , 
		n.BureauAdverses as BureauAdverses , 
		n.Bureau as Bureau , 
		n.InitialTOBRate as InitialTOBRate, 
		n.InitialTOBDecision as InitialTOBDecision, 
		n.InitialTOBTerm as InitialTOBTerm, 
		n.InitialTOBNVFNetLoan as InitialTOBNVFNetLoan, 
		n.InitialTOBCaTotalLimit as InitialTOBCaTotalLimit,
		n.InitialTOBScoreNGR as InitialTOBScoreNGR, 
		n.InitialTOBScoreScaled as InitialTOBScoreScaled, 
		n.InitialTOBBureauScore as InitialTOBBureauScore,
		n.FinalTOBRate as FinalTOBRate, 
		n.FinalTOBDecision as FinalTOBDecision, 
		n.FinalTOBTerm as FinalTOBTerm, 
		n.FinalTOBNVFNetLoan as FinalTOBNVFNetLoan, 
		n.FinalTOBCaTotalLimit as FinalTOBCaTotalLimit,
		n.FinalTOBScoreNGR as FinalTOBScoreNGR, 
		n.FinalTOBScoreScaled as FinalTOBScoreScaled,
		n.FinalTOBBureauScore as FinalTOBBureauScore, 
		n.TakenUpTOBRate as TakenUpTOBRate, 
		n.TakenUpTOBDecision as TakenUpTOBDecision, 
		n.TakenUpTOBTerm as TakenUpTOBTerm, 
		n.TakenUpTOBNVFNetLoan as TakenUpTOBNVFNetLoan, 
		n.TakenUpTOBCaTotalLimit as TakenUpTOBCaTotalLimit, 
		n.TakenUpTOBScoreNGR as TakenUpTOBScoreNGR, 
		n.TakenUpTOBScoreScaled as TakenUpTOBScoreScaled, 
		n.ApplicationId as ApplicationId_f_lu_m_TermsOfBusiness  , 
		dbo.fn_fc_AppealReLook_Fact(q.originalapplicationid) as f_lu_m_appealrelook_fact ,
		q.Relook_sum as Relook_sum , 
		q.max_AppealCount as max_AppealCount , 
		q.ChangeSupportingDocumetsdetails_sum as ChangeSupportingDocumetsdetails_sum , 
		q.ChangeSecurityinformation_sum as ChangeSecurityinformation_sum , 
		q.ChangePaymentMediumDetails_sum as ChangePaymentMediumDetails_sum , 
		q.ChangeLoaninformation_sum as ChangeLoaninformation_sum , 
		q.ChangeDealerinformation_sum as ChangeDealerinformation_sum , 
		q.ChangeAssetinformation_sum as ChangeAssetinformation_sum ,
		q.ChangeApplicationinformation_sum as ChangeApplicationinformation_sum , 
		q.AReasonX as AReasonX , 
		q.AReason5 as AReason5 , 
		q.AReason4 as AReason4 , 
		q.AReason3 as AReason3 , 
		q.AReason2 as AReason2 , 
		q.AReason1 as AReason1 ,
		q.Appealrelookstaffware_sum as Appealrelookstaffware_sum , 
		dbo.fn_fc_RateChange(h.originalapplicationid) as f_lu_m_RateChange ,
		h.[user] as User_RateChg ,
		h.Updated as Updated_RateChg ,
		h.RateAdjustmentId as RateAdjustmentId , 
		h.newInterestRate as newInterestRate , 
		h.Decision as Decision , 
		h.DateCaptured as DateCaptured , 
		h.Comments as Comments_RateChange , 
		h.approvedInterestRate as approvedInterestRate , 
		h.ApplicationId as ApplicationId_f_lu_m_RateChange , 
		dbo.fn_fc_DocumentationFeeChange(c.originalapplicationid) as f_lu_m_DocumentationFeeChange ,
		c.[user] as User_DocFeeChg , 
		c.Updated as Updated_DocFeeChg , 
		c.OptionDecision as OptionDecision , 
		c.NewDocumentationFee as NewDocumentationFee , 
		c.DocumenationFeeOptionId as DocumentationFeeOptionId , 
		c.DocumentationFeeDecision as DocumentationFeeDecision , 
		c.DocumentationFeeAdjustmentId as DocumentationFeeAdjustmentId , 
		c.Date as Date_DocfeeChrg , 
		c.Comments as Comments_DocFeeChrg , 
		c.ApprovedDocumentationFee as ApprovedDocumentationFee , 
		c.ApplicationId as ApplicationId_f_lu_m_DocumentationFeeChange , 
		dbo.fn_fc_PaymentMedium(i.originalapplicationid) as f_lu_m_PaymentMedium  ,
		i.OtherAccountNumber as OtherAccountNumber , 
		i.IsNedbankCA as IsNedbankCA , 
		i.IsCurrentAccount as IsCurrentAccount , 
		i.ClearingCode as ClearingCode , 
		i.BranchName as BranchName , i.Bank as Bank , 
		i.ApplicationId as ApplicationId_f_lu_m_PaymentMedium , 
		i.AccountTypeId as AccountTypeId , 
		i.AccountNumber as AccountNumber , 
		dbo.fn_fc_AppFreeze(f.originalapplicationid) as f_lu_m_Freeze ,
		f.[user] as User_Freeze , 
		f.Updated as Updated_Freeze , 
		f.FreezeReason5 as FreezeReason5 , 
		f.FreezeReason4 as FreezeReason4 , 
		f.FreezeReason3 as FreezeReason3 , 
		f.FreezeReason2 as FreezeReason2 , 
		f.FreezeReason1 as FreezeReason1 ,
		f.FreezeId as FreezeId , 
		f.[Date] as Date_Freeze , 
		f.Comments as Comments_Freeze , 
		f.ApplicationId as ApplicationId_f_lu_m_Freeze , 
		dbo.fn_fc_CashFlow (g.originalapplicationid) as f_lu_m_CashFlow  ,
		g.TotalDebtors as TotalDebtors_CashFlow , 
		g.TotalCurrentLiabilities as TotalCurrentLiabilities_CashFlow , 
		g.TotalCurrentAssets as TotalCurrentAssets_CashFlow , 
		g.TotalCreditors as TotalCreditors_CashFlow , 
		g.PositiveOpeningBankBalance as PositiveOpeningBankBalance , 
		g.OpningStock as OpeningStock_CashFlow , 
		g.NetProfitMonth9 as NetProfitMonth9 , 
		g.NetProfitMonth8 as NetProfitMonth8 , 
		g.NetProfitMonth7 as NetProfitMonth7 , 
		g.NetProfitMonth6 as NetProfitMonth6 ,	
		g.NetProfitMonth5 as NetProfitMonth5 , 
		g.NetProfitMonth4 as NetProfitMonth4 , 
		g.NetProfitMonth3 as NetProfitMonth3 , 
		g.NetProfitMonth2 as NetProfitMonth2 , 
		g.NetProfitMonth12 as NetProfitMonth12 , 
		g.NetProfitMonth11 as NetProfitMonth11 , 
		g.NetProfitMonth10 as NetProfitMonth10 , 
		g.NetProfitMonth1 as NetProfitMonth1 , 
		g.NetProfitActual as NetProfitActual , 
		g.NegativeOpeningBankBalance as NegativeOpeningBankBalance , 
		g.DisposableCashMonth9 as DisposableCashMonth9 , 
		g.DisposableCashMonth8 as DisposableCashMonth8 , 
		g.DisposableCashMonth7 as DisposableCashMonth7 , 
		g.DisposableCashMonth6 as DisposableCashMonth6 , 
		g.DisposableCashMonth5 as DisposableCashMonth5 , 
		g.DisposableCashMonth4 as DisposableCashMonth4 , 
		g.DisposableCashMonth3 as DisposableCashMonth3 , 
		g.DisposableCashMonth2 as DisposableCashMonth2 , 
		g.DisposableCashMonth12 as DisposableCashMonth12 , 
		g.DisposableCashMonth11 as DisposableCashMonth11 , 
		g.DisposableCashMonth10 as DisposableCashMonth10 , 
		g.DisposableCashMonth1 as DisposableCashMonth1 , 
		g.DisposableCashActual as DisposableCashActual , 
		g.CashFlowTurnoverActual as CashFlowTurnoverActual , 
		g.CashFlowMonth9 as CashFlowMonth9 , 
		g.CashFlowMonth8 as CashFlowMonth8 , 
		g.CashFlowMonth7 as CashFlowMonth7 , 
		g.CashFlowMonth6 as CashFlowMonth6 , 
		g.CashFlowMonth5 as CashFlowMonth5 , 
		g.CashFlowMonth4 as CashFlowMonth4 , 
		g.CashFlowMonth3 as CashFlowMonth3 , 
		g.CashFlowMonth2 as CashFlowMonth2 , 
		g.CashFlowMonth12 as CashFlowMonth12 , 
		g.CashFlowMonth11 as CashFlowMonth11 , 
		g.CashFlowMonth10 as CashFlowMonth10 , 
		g.CashFlowMonth1 as CashFlowMonth1 , 
		g.AverageCreditTurnover as AverageCreditTurnover , 
		g.ApplicationId as ApplicationId_f_lu_m_CashFlow, 
		dbo.fn_fc_CreditTurnover(b.originalapplicationid) as f_lu_m_CreditTurnover,
		b.Turnover as Turnover , 
		b.Deposit5 as Deposit5 ,
		b.Deposit4 as Deposit4 , 
		b.Deposit3 as Deposit3 , 
		b.Deposit2 as Deposit2 , 
		b.Deposit1 as Deposit1 , 
		b.Deposit as Deposit_CreditTurnover , 
		b.Credits5 as Credits5 , 
		b.Credits4 as Credits4 , 
		b.Credits3 as Credits3 , 
		b.Credits2 as Credits2 , 
		b.Credits1 as Credits1 , 
		b.Credits as Credits , 
		b.ApplicationId as ApplicationId_f_lu_m_CreditTurnover , 
		
		dbo.fn_fc_AssetsLiabilities(e.originalapplicationid) as f_lu_m_AssetsLiabilities ,
		e.TotalDebtors as TotalDebtors_AssetsLiabilities , 
		e.TotalCurrentLiabilities as TotalCurrentLiabilities_AssetsLiabilities , 
		e.TotalCurrentAssets as TotalCurrentAssets_AssetsLiabilities , 
		e.TotalCreditors as TotalCreditors_AssetsLiabilities , 
		e.OpeningStock as OpeningStock_AssetsLiabilities , 
		e.BankBalanceLiability as BankBalanceLiability , 
		e.BankBalanceAsset as BankBalanceAsset , 
		e.ApplicationId as ApplicationId_f_lu_m_AssetsLiabilities , 
		dbo.fn_fc_CALoanDetails (j.originalapplicationid) as _lu_m_CALoanDetails ,
		j.TotalLimit as TotalLimit , 
		j.SourceOfRepayment as SourceOfRepayment_CALoanDet , 
		j.RequiredRepayment as RequiredRepayment , 
		j.RequiredRate as RequiredRate , 
		j.RequestDescription as RequestDescription , 
		j.RepaymentPeriod as RepaymentPeriod_CALoanDet , 
		j.ReductionTypeId as ReductionTypeId , 
		j.ReductionStartDate as ReductionStartDate_CALoanDetails , 
		j.ReductionAmount as ReductionAmount_CALoanDetails , 
		j.ReduceTo as ReduceTo , 
		j.PurposeOfFacility as PurposeOfFacility , 
		j.MustFacilityReduce as MustFacilityReduce , 
		j.Motivation as Motivation_CALoanDet , 
		j.GuaranteeAmount as GuaranteeAmount , 
		j.ExpiryDate as ExpiryDate_CALoanDet , 
		j.ExistingRepaymentAmount as ExistingRepaymentAmount , 
		j.ExistingRepayment as ExistingRepayment , 
		j.ExistingExpiryDate as ExistingExpiryDate , 
		j.CurrentLimit as CurrentLimit_CALoanDetails , 
		j.BeneficiaryDetails as BeneficiaryDetails , 
		j.ApplicationId as ApplicationId_f_lu_m_CALoanDetails  , 
		j.AdditionalLimit as AdditionalLimit_CALoanDetails , 
		dbo.fn_fc_NVFAsset(l.originalapplicationid) as  f_lu_m_NVFAsset ,
		l.[Year] as Year_ , 
		l.TradeValue4 as TradeValue4 , 
		l.TradeValue3 as TradeValue3 , 
		l.TradeValue2 as TradeValue2 , 
		l.TradeValue1 as TradeValue1 ,
		l.TradeValue as TradeValue , 
		l.SpecifyOther as SpecifyOther , 
		l.RetailValue4 as RetailValue4 , 
		l.RetailValue3 as RetailValue3 , 
		l.RetailValue2 as RetailValue2 , 
		l.RetailValue1 as RetailValue1 , 
		l.RetailValue as RetailValue , 
		l.Model as Model , 
		l.MMCode as MMCode , 
		l.Make as Make , 
		l.KM as KM , 
		l.IsNew as IsNew , 
		l.ExtrasValueOther as ExtrasValueOther , 
		l.ExtrasValue5 as ExtrasValue5 , 
		l.ExtrasValue4 as ExtrasValue4 , 
		l.ExtrasValue3 as ExtrasValue3 , 
		l.ExtrasValue2 as ExtrasValue2 , 
		l.ExtrasValue1 as ExtrasValue1 , 
		l.ExtrasDescriptionOther as ExtrasDescriptionOther , 
		l.ExtrasDescription5 as ExtrasDescription5 , 
		l.ExtrasDescription4 as ExtrasDescription4 , 
		l.ExtrasDescription3 as ExtrasDescription3 , 
		l.ExtrasDescription2 as ExtrasDescription2 , 
		l.ExtrasDescription1 as ExtrasDescription1 , 
		0 as ExpectedKM , 
		l.Condition as Condition , 
		l.AssetId as AssetId , 
		l.ApplicationId as ApplicationId_f_lu_m_NVFAsset , 
		dbo.fn_fc_TermLoan(d.originalapplicationid) as f_lu_m_TermLoan ,
		d.SourceOfRepayment as SourceOfRepayment_TermLoan , 
		d.RepaymentPeriod as RepaymentPeriod_TermLoan , 
		d.RateWithinPolicy as RateWithinPolicy , 
		d.RateTypeId as RateTypeId , 
		d.RateAuthorisation as RateAuthorisation , 
		d.Rate as Rate_TermLoan , 
		d.PaymentFrequencyId as PaymentFrequencyId , 
		d.Motivation as Motivation_TermLoan , 
		d.LoanAmount as LoanAmount , 
		d.FirstPaymentDate as FirstPaymentDate ,
		d.FinanceTypeId as FinanceTypeId , 
		0 as DocumentationFeeId , 
		d.ArrearsAdvanceId as ArrearsAdvanceId , 
		d.ApplicationId as ApplicationId_f_lu_m_TermLoan ,
		dbo.fn_fc_ApplicationState_Fact(s.originalapplicationid) as f_lu_m_ApplicationState_fact  ,
		s.Asses_sub as Asses_sub , 
		s.ApplicationId as  ApplicationId_f_lu_m_ApplicationState_fact , 
		s.StaffwareCase as StaffwareCase , 
		s.First_ApplicationStateId as First_ApplicationStateId , 
		s.Last_ApplicationStateId as Last_ApplicationStateId , 
		s.AssessmentComments as AssessmentComments , 
		s.StaffwareStateId as StaffwareStateId , 
		s.TransactStateId as TransactStateId , 
		s.StaffwareSubmitDate as StaffwareSubmitDate , 
		s.TransactSubmitDate as TransactSubmitDate , 
		s.ApplicationDecisionId as ApplicationDecisionId , 
		s.PreAppealStateId as PreAppealStateId , 
		0 as f_lu_m_ContractualDebt_fact ,
		0 as ApplicantId , 
		0 as NVFInstallment , 
		0 as HLInstallment ,
		0 as NVFSettlement , 
		0 as HLSettlement , 
		0 as OverdraftOther , 
		0 as CreditCardOther , 
		0 as OverdraftSettlement , 
		t.Total as NetMonthlyIncome , 
		0 as TotalExpenses , 
		0 as TotalLoanPayments , 
		dbo.fn_fc_AccountDetails_fact(v.originalapplicationid)as f_lu_m_AccountDetails_fact ,
		v.ApplicationId as ApplicationId_AccountDetails , 
		v.CIS as CIS_AccountDetails , 
		v.BrandId as BrandId , 
		v.Dcar as Dcar , 
		v.MarketSegment as MarketSegment_AccountDetails , 
		v.MarketSector as MarketSector_AccountDetails , 
		v.CANumber as CANumber , 
		v.NVFNumber as NVFNumber , 
		v.HLNumber as HLNumber , 
		v.SANumber as SANumber , 
		v.HasPersonalLoan as HasPersonalLoan , 
		v.PersonalLoanNumber as PersonalLoanNumber , 
		v.IsBusinessApplication as IsBusinessApplicaition , 
		v.IsPrincipleApplication as IsPrincipleApplication , 
		dbo.fn_fc_DecisionUpdate_fact(w.originalapplicationid) as f_lu_m_DecisionUpdate_fact ,
		w.Applicationid as Applicationid_Final_DecisionUpdate , 
		w.DecisionUpdateId as DecisionUpdateId , 
		w.ApplicationId as ApplicationId , 
		w.FinalVerificationOutcomeId as FinalVerificationOutcomeId , 
		w.FinalAppealDecisionId as FinalAppealDecisionId , 
		w.AppealAllowed as AppealAllowed , 
		w.AppealAssessmentComments as AppealAssessmentComments , 
		w.OverrideReasonId as OverrideReasonId , 
		w.DecisionUser as DecisionUser , 
		w.DecisionDate as DecisionDate , 
		w.AppealCounter as AppealCounter , 
		w.TotalApprovedLimit as TotalApprovedLimit , 
		w.PurchasePrice as PurchasePrice , 
		w.Installment as Installment_Final_DecisionUpdate , 
		w.Rate as Rate_Final_DecisionUpdate , 
		w.Term as Terlu_m_Final_DecisionUpdate , 
		w.DepositAmount as DepositAmount_Final_DecisionUpdate , 
		w.BalloonAmount as BalloonAmount_Final_DecisionUpdate , 
		w.LoanToValue as LoanToValue_Final_DecisionUpdate , 
		w.ChangeTOB as ChangeTOB , 
		w.CurrentLimit as CurrentLimit_Final_Final_DecisionUpdate , 
		w.AdditionalLimit as AdditionalLimit_Final_DecisionUpdate , 
		w.MonthlyReductionAmount as MonthlyReductionAmount , 
		w.OnceOffExpiryDate as OnceOffExpiryDate , 
		w.ReduceBackTo as ReduceBackTo , 
		w.ReductionStartDate as ReductionStartDate_Final_DecisionUpdate , 
		w.ReductionAmount as ReductionAmount_Final_DecisionUpdate , 0 as IsIncomeValidated ,
		w.DeclineReasonId as DeclineReasonId,
		w.InitialDecisionUser as InitialDecisionUser, 
		w.InitialAdditionalLimit as InitialAdditionalLimit,
		w.InitialFinalAppealDecisionid as InitialFinalAppealDecisionid, 
		w.InitialTotalApprovedlimit as InitialTotalApprovedlimit,
		w.InitialOverrideReasonId as InitialOverrideReasonId, 
		w.InitialRate as InitialRate, 
		w.finalDecisionUser as finalDecisionUser,
		w.FinalAdditionalLimit as FinalAdditionalLimit,	
		w.FinalFinalAppealDecisionid as FinalFinalAppealDecisionid,
		w.FinalTotalApprovedlimit as FinalTotalApprovedlimit,
		w.FinalOverrideReasonId as FinalOverrideReasonId, 
		w.FinalRate as FinalRate,
		dbo.fn_fc_NVFLoanDetails_fact(x.originalapplicationid) as  f_lu_m_NVFLoanDetails_fact  ,
		x.DepositAmount as NVF_Deposit_Amount, 
		x.PercentageDeposit as NVF_Deposit_Perc, 
		x.IsSchemeClient as IsSchemeClient,
		dbo.fn_fc_CanDecision(w.applicationid,n.applicationid,w.DecisionUpdateId,n.decisioncode) as CAN_DECISION,
		dbo.fn_fc_Can_Decision_Source(w.applicationid,n.applicationid) as Can_Decision_source,
		dbo.fn_fc_Rule_Finaldecision(w.finalappealdecisionid,n.decisioncode) as Rule_Finaldecision,
		dbo.fn_fc_Rule_FinalLimitOffered(w.totalapprovedlimit,n.CaTotalLimit) as Rule_FinalLimitOffered,
		dbo.fn_fc_Rule_FinalRate(h.newinterestrate,w.finalrate,n.FinalTobRate) as Rule_FinalRate,
		dbo.fn_fc_Rule_RateDifference(n.finaltobrate,w.finalrate) as Rule_RateDifference,
		dbo.fn_fc_Rule_Rule_LargeLimit(w.totalapprovedlimit,n.CaTotalLimit,j.mustfacilityReduce) as Rule_LargeLimit
		
--</S>--
from	
--<F>--
		dbo.fc_Master_Lookup_Application_Fact a 
		left outer join 
		dbo.fc_Master_Lookup_CreditTurnover b 

on		a.originalapplicationid = b.originalapplicationid and 
		a.srv = b.srv
		left outer join 
		dbo.fc_Master_Lookup_DocumentationFeeChange c 

on		a.originalapplicationid = c.originalapplicationid and 
		a.srv = c.srv
		left outer join 
		dbo.fc_Master_Lookup_TermLoan d 

on		a.originalapplicationid = d.originalapplicationid and
		a.srv = d.srv
		left outer join 
		dbo.fc_Master_Lookup_AssetsLiabilities e 

on		a.originalapplicationid = e.originalapplicationid and 
		a.srv = e.srv
		left outer join 
		dbo.fc_Master_Lookup_AppFreeze f 

on		a.originalapplicationid = f.originalapplicationid and 
		a.srv = f.srv
		left outer join 
		dbo.fc_Master_Lookup_CashFlow g 

on		a.originalapplicationid = g.originalapplicationid and 
		a.srv = g.srv
		left outer join 
		dbo.fc_Master_Lookup_RateChange h 

on		a.originalapplicationid = h.originalapplicationid and 
		a.srv = h.srv
		left outer join 
		dbo.fc_Master_Lookup_PaymentMedium i 

on		a.originalapplicationid = i.originalapplicationid and 
		a.srv = i.srv
		left outer join 
		dbo.fc_Master_Lookup_CALoanDetails j 

on		a.originalapplicationid = j.originalapplicationid and 
		a.srv = j.srv
		left outer join 
		dbo.fc_Master_Lookup_AppStructure k 

on		a.originalapplicationid = k.originalapplicationid and 
		a.srv = k.srv
		left outer join 
		dbo.fc_Master_Lookup_NVFAsset l 

on		a.originalapplicationid = l.originalapplicationid and 
		a.srv = l.srv
		left outer join 
		dbo.fc_Master_Lookup_TOB n 

on		a.originalapplicationid = n.originalapplicationid and 
		a.srv = n.srv
		left outer join 
		dbo.fc_Master_Lookup_Initiation_Source o 

on		a.originalapplicationid = o.originalapplicationid and 
		a.srv = o.srv
		left outer join 
		dbo.fc_Master_AppealReLook_Fact q 

on		a.originalapplicationid = q.originalapplicationid and 
		a.srv = q.srv
		left outer join 
		dbo.fc_Master_Lookup_Applicant_Fact r 

on		a.originalapplicationid = r.originalapplicationid and 
		a.srv = r.srv
		left outer join 
		dbo.fc_Master_Lookup_ApplicationState_Fact s 

on		a.originalapplicationid = s.originalapplicationid and 
		a.srv = s.srv
		left outer join dbo.fc_Master_Lookup_IncomeCalculator t 

on		a.originalapplicationid = t.originalapplicationid  and 
		a.srv = t.srv
		left outer join dbo.fc_Master_Lookup_AccountDetails v 

on		a.originalapplicationid = v.originalapplicationid and 
		a.srv = v.srv
		left outer join 
		dbo.fc_Master_Lookup_Final_DecisionUpdate w 

on		a.originalapplicationid = w.originalapplicationid and 
		a.srv = w.srv
		left outer join 
		dbo.fc_Master_Lookup_NVFLoanDetails x

on		a.originalapplicationid = x.originalapplicationid and 
		a.srv = x.srv
--</F>--

CREATE UNIQUE CLUSTERED INDEX
--<X>--
[IX_PK_appdate] ON dbo.FACT_CAN
([Min_Datecaptured],[OriginalApplicationId] ASC)WITH (STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF) ON [PRIMARY]
--</X>--
--</BLK>--